Oracle REPLACE function - uncertainty about how to use for my script

Question

I have a column with a name THE_VALUEin a table TABLE_Athat contains data similar to the following, i.e. several lines of selection can 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 those that are in brackets with a colon, so basically the end result will be:

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

I also want to make sure there are no spaces after the colon after the update.

I tried using the function replace, but I'm not sure how to not include commas in brackets, since I don't want them to change to a colon.

Thanks.

Answer 1

PL/SQL, :

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;

:

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

- Oracle RegEx. , .

Answer 2

, , REPLACE. REGEXP_REPLACE.

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

- :)

Answer 3

It looks like you might need a regular expression for your needs:
http://www.oracle.com/technology/obe/obe10gdb/develop/regexp/regexp.htm
Regular expressions in stored procedures