string manupilation

Hi ,

I need to strip special characters (<< , >> ,<,>) from an string and also replace characters like \,_,) with \\ and \_ to maintain their special meaning.

Below is the query iam using , but it replaces all the special characters and returns only the alpha numeric string.

Select regexp_replace('<<sam*>>','[^[:alnum:]]') from dual;

Output :sam.

I need an output like this :sam*

If input string is : <sam> , output :sam

if input string is : <<sam*/>> output : sam*\\ -- escape sequence to override the special meaning of \

Any help is really appreciated.
thanks in advance
rgds
sam
sam_2012Asked:
Who is Participating?
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
You should be able to escape a list of single characters all with one regexp_replace but not do all the other replaces you want with a single call.

For example, just escaping the special characters:
drop table tab1 purge;
create table tab1 (col1 varchar2(20));

set define off
insert into tab1 values('this & that');
insert into tab1 values('a|b,c');
set define off
commit;

select regexp_replace(col1,'([|,.\/&])','\\\1') from tab1;

Open in new window


You will need to add the replacements for '<', '>', etc...
0
 
slightwv (䄆 Netminder) Commented:
I would do individual replaces for each rule.

I'm not sure that you can come up with a regular expression that will handle all the requirements in a single statement.
0
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
For example:
Select
   replace(
        replace('<<sam*>>','<'),
     '>')
 from dual;
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
sdstuberCommented:
<<sam*/>> output : sam*\\


typo or new requirement?

the slash direction reversed
0
 
sdstuberConnect With a Mentor Commented:
I'm assuming the / above was a typo and it was supposed to be \

be careful how you nest your replaces.  Do \ before _ or any other escaped characters
otherwise you'll escape your own escapes


Try this...
SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(str, '\', '\\'), '_', '\_'), ')', '\)'), '<'), '>')
  FROM yourtable


Also note, these are easier cases because they are single character replaces.
If you were adjusting for multi-character substrings then you'd have to pay additional care to the order of the processing.  For example, you'd want the < and > removal to be done last so you didn't accidentally create connected strings
0
 
sam_2012Author Commented:
Hi sdstuber,

I have to escape sequence even \\ , || . My requirement is I need to add escape sequence \ to special characters like  || , . , \\ ,//,/ & .

can this be achieved using single replace ?
0
 
sdstuberCommented:
>>> can this be achieved using single replace ?

no
0
 
sdstuberCommented:
>>> list of single characters

some are, some aren't

 ||
\\
//

so the regexp above

[|,.\/&]

would not handle those correctly, resulting in...

\|\|
\\\\
\/\/

but, it's not intended to, as slightwv said it's for SINGLE characters.
so, since you have some multi-character strings, it should probably be...

[,.&]
0
 
sam_2012Author Commented:
awesome.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.