Solved

string manupilation

Posted on 2014-02-12
9
312 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 77

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 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 300 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 200 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 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 300 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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

Suggested Solutions

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

739 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