Oracle REPLACE function - unsure how to use for my scenario

Question

Hi,

I have a column called THE_VALUE in a table TABLE_A, that holds data similar to the following, i.e a few sample rows might be:

tom:harry, sally, jeff
state(vic,nsw), england, qwerty(aaa,bbb, cccc):qaz

What I need to do to update this column using Oracle 10g sql and replace all commas, except the ones within the brackets with a colon, so basically, end result would be:

tom:harry:sally:jeff
state(vic,nsw):england:qwerty(aaa,bbb, cccc):qaz

I also want to ensure that there are no spaces after the colons, after the update.

I've tried using the replace function but I am unsure as to how not to include the commas within the brackets, as I don't want these changed to colons.

Thanks.

Answer 1

You can not do what you want with the REPLACE function. However you can try REGEXP_REPLACE function.

http://www.regular-expressions.info/oracle.html

As a programmer joke says - now you have two problems :)

Answer 2

Looks like you might need regex for your needs:
http://www.oracle.com/technology/obe/obe10gdb/develop/regexp/regexp.htm
http://stackoverflow.com/questions/1650018/regular-expressions-in-stored-procedures/

Answer 3

Here's a PL/SQL function I did on the quick:

create or replace function fix_comma(str varchar2) return varchar2
is
   strLen smallint := length(str);
   cntPar smallint := 0;
   c char;
   strOut varchar2(4000) := '';
   lastWasComma boolean := false;
begin
   for i in 1..strLen loop
      c := substr(str, i, 1);
      if c = '(' then
         cntPar := cntPar + 1;
         lastWasComma := false;
      elsif c = ')' then
         if cntPar > 0 then
            cntPar := cntPar - 1;
         end if;
         lastWasComma := false;
      elsif cntPar = 0 and c = ',' then
         c := ':';
         lastWasComma := true;
      elsif cntPar = 0 and c = ' ' and lastWasComma then
         c := null;
      else
         lastWasComma := false;
      end if;

      strOut := strOut || c;
   end loop;
   return strOut;
end;

select fix_comma('state(vic,nsw), england, qwerty(aaa,bbb, cccc):qaz') from dual
union
select fix_comma('state(tik (vic,nsw) tok))),   england,   qwerty(aaa,  bbb, cccc):qaz') from dual;

It outputs:

state(vic,nsw):england:qwerty(aaa,bbb, cccc):qaz
state(tik (vic,nsw) tok))):england:qwerty(aaa,  bbb, cccc):qaz

Try writing something similar using Oracle RegEx. I know I gave up.