Home » SQL & PL/SQL » SQL & PL/SQL » Replace (Oracle 11G)
Replace [message #589409] Fri, 05 July 2013 05:33 Go to next message
nimal
Messages: 2
Registered: July 2013
Junior Member
Hi,

I'm trying to replace a string between "H=" and the next ",".
The SQL should replace value 10103047 to _unknown, but I must be doing something wrong; it doesn't work.

What is wrong with my statement?

SQL> select replace('H=10103047,S=00,Y=2,h=2,z=-2,q=0','(.*H=)|(,.*)','_unknown') "replace" from dual
2 /

Please advise.

Niels.

[Updated on: Fri, 05 July 2013 05:34]

Report message to a moderator

Re: Replace [message #589413 is a reply to message #589409] Fri, 05 July 2013 05:43 Go to previous messageGo to next message
Littlefoot
Messages: 18822
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I guess you meant REGEXP_REPLACE, not REPLACE. Anyway, it doesn't work. Something like this, maybe?
SQL> select regexp_replace('H=10103047,S=00,Y=2,h=2,z=-2,q=0', '^H=\d+', '_unknown') result
  2  from dual;

RESULT
------------------------------
_unknown,S=00,Y=2,h=2,z=-2,q=0

SQL>
Re: Replace [message #589414 is a reply to message #589409] Fri, 05 July 2013 05:46 Go to previous messageGo to next message
_jum
Messages: 485
Registered: February 2008
Senior Member
Have a look at the Regular Expressions in Oracle
select regexp_replace('H=10103047,S=00,Y=2,h=2,z=-2,q=0','(.*?H=).*(,.*)','\1_unknown\2') replace
  from dual;

REPLACE                                                                         
--------------------------------------------------------------------------------
H=_unknown,S=00,Y=2,h=2,z=-2,q=0                                                
1 row selected.

There are a lot of other (more easy) ways to get the desired result.

[Updated on: Fri, 05 July 2013 05:47]

Report message to a moderator

Re: Replace [message #589415 is a reply to message #589413] Fri, 05 July 2013 05:46 Go to previous message
nimal
Messages: 2
Registered: July 2013
Junior Member
Actually, you're right; I meant REGEXP_REPLACE.

Thanks very much! It works!
Previous Topic: Counting followed last ocurrences
Next Topic: No data found
Goto Forum:
  


Current Time: Thu Apr 17 00:35:19 CDT 2014

Total time taken to generate the page: 0.10600 seconds