Solved

string manupilation

Posted on 2014-02-12
9
313 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

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…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

617 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