Question

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

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.

Answer 2

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 3

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

友情链接