Solved

string manupilation

Posted on 2014-02-12
9
310 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 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
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.
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…

828 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