Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

string manupilation

Posted on 2014-02-12
9
Medium Priority
?
316 Views
Last Modified: 2014-02-19
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
0
Comment
Question by:sam_2012
  • 4
  • 3
  • 2
9 Comments
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39853617
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
 
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 1200 total points
ID: 39853621
For example:
Select
   replace(
        replace('<<sam*>>','<'),
     '>')
 from dual;
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39853792
<<sam*/>> output : sam*\\


typo or new requirement?

the slash direction reversed
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 800 total points
ID: 39853833
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
 

Author Comment

by:sam_2012
ID: 39856632
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 39856652
>>> can this be achieved using single replace ?

no
0
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1200 total points
ID: 39856747
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 39856768
>>> 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
 

Author Closing Comment

by:sam_2012
ID: 39872179
awesome.
0

Featured Post

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Suggested Courses

783 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question