OraFAQ Forum: SQL & PL/SQL » Replace string

Home » SQL & PL/SQL » SQL & PL/SQL » Replace string (oracle 10.2.0.3)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Replace string [message #487895]

Mon, 03 January 2011 04:43

Its_me_ved
Messages: 979
Registered: October 2009
Location: India

Senior Member

My requirement is something like below:

If a string contains below character then precede by \with the character:

Chracters that needs to be replaced are:
",',\ and control characters.

eg,


with t as 
(select 'a"b"c\d''e' col from dual)
select * from t;

a"b"c\d'e

Expected output: precede those character by \
i.e,

a\"b\"c\\d\'e

I tried something this way:


SQL>  create or replace FUNCTION funcstrip(l_text in VARCHAR2)
  2    RETURN VARCHAR2
  3    IS
  4      l_return VARCHAR2(4000):='  ';
  5    BEGIN
  6      /* If string contains ",' or \ then precede it with \ to escape values for JSON.Need to incl
  7  */
  8      if instr(l_text,CHR(34))>0 or instr(l_text,CHR(39))>0 or instr(l_text,CHR(92))>0 then
  9      For i In 1..length(l_text)
 10      Loop
 11        IF ASCII(substr(l_text,i,1)) in (34,39,92) Then
 12          l_return := replace(l_text,substr(l_text,i,1),'\'||substr(l_text,i,1));
 13        END IF;
 14      End Loop;
 15      l_return:=l_return;
 16      else
 17      l_return:=l_text;
 18      end if;
 19      Return l_return;
 20    END;
 21   /

Function created.

SQL>  with t as ( select 'al"l"k"zl' a from dual) select funcstrip(a) from t;

FUNCSTRIP(A)
------------
al\"l\"k\"zl

SQL> with t as ( select 'al\l\k\zl' a from dual) select funcstrip(a) from t
 /

FUNCSTRIP(A)
------------
al\\l\\k\\zl

SQL> with t as ( select 'allkzl' a from dual) select funcstrip(a) from t
/

FUNCSTRIP(A)
-----------
allkzl


SQL> with t as ( select 'all"k\zl' a from dual) select funcstrip(a) from t
/

FUNCSTRIP(A)
------------
all"k\\zl --WRONG

My replace logic appears to be wrong. Specially the l_test inside the loop.Any suggesion will help.

Regards
Ved

Report message to a moderator

Re: Replace string [message #487904 is a reply to message #487895]

Mon, 03 January 2011 05:08

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

SQL> with t as (select 'a"b"c\d''e' col from dual)
  2  select col, 
  3         regexp_replace(col,'(["''\\])', '\\\1') new_col
  4  from t;
COL        NEW_COL
---------- --------------------
a"b"c\d'e  a\"b\"c\\d\'e

Regards
Michel

Report message to a moderator

Re: Replace string [message #487908 is a reply to message #487895]

Mon, 03 January 2011 05:12

flyboy
Messages: 1903
Registered: November 2006

Senior Member

I would check whether the " character has the ASCII code 34, or whether it is not the special one (e.g Windows ˝ = ASCII 733 in my code page).

Anyway, your approach is really flawed when multiple special characters are present (e.g. in your first example) - it replaces them as many times as they occur in the input string (as REPLACE replaces all occurrences of given pattern). I would code it e.g. like this

begin
  l_return := l_text;
  IF instr(l_text,CHR(34))>0 Then
    l_return := replace(l_return, chr(34),'\'||chr(34));
  END IF;
  IF instr(l_text,CHR(39))>0 Then
    l_return := replace(l_return, chr(39),'\'||chr(39));
  END IF;
  IF instr(l_text,CHR(92))>0 Then
    l_return := replace(l_return, chr(92),'\'||chr(92));
  END IF;
end;
/

(it would be necessary to let '\' as the last replacement). I did not test this, as I would simply use REGEXP_REPLACE:

with t as (select 'a"b"c\d''e' col from dual
 union all select 'all"k\zl' a from dual)
select col, regexp_replace( col, '([\''"])', '\\\1' ) from t;

Report message to a moderator

Re: Replace string [message #487909 is a reply to message #487904]

Mon, 03 January 2011 05:15

Its_me_ved
Messages: 979
Registered: October 2009
Location: India

Senior Member

Thanks. Nice one! Trying to figure out where I was wrong.
Thanks you very much.It solves my problem.

Regards
Ved

Report message to a moderator

Re: Replace string [message #487912 is a reply to message #487908]

Mon, 03 January 2011 05:21

Its_me_ved
Messages: 979
Registered: October 2009
Location: India

Senior Member

Thanks flyboy for pointing out the mistake. I was trying to do in a single shot.

Thanks to Michel and You. It solves my problem.

SQL>  with t as (select 'a"b"c\d''e'||chr(11)||'b' col from dual)
  2      select col, 
  3             regexp_replace(col,'(["''[:cntrl:]\\])', '\\\1') new_col
  4     from t;

COL                                          NEW_COL
-------------------------------------------- -------------------------------------------------------
a"b"c\d'eb                                  a\"b\"c\\d\'e\b

SQL>

Regards
Ved

[Updated on: Mon, 03 January 2011 05:32]

Report message to a moderator

Previous Topic:	A pl/sql question need help
Next Topic:	Mview Refresh

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Aug 27 15:20:02 CDT 2025