Home » SQL & PL/SQL » SQL & PL/SQL » replacing certain characters in string
replacing certain characters in string [message #190844] Fri, 01 September 2006 09:43 Go to next message
123soleil
Messages: 35
Registered: July 2006
Member
Hello,

Sorry for the dumbness of my question, but I would like to replace all the "." and "," with "_". (These characters could apear, but not necessarily)

this should work: select (replace(replace(myCol,'.','_'),',','_')

However I have 5 different characters that I need to replace with "_" so I'm looking for an easier solution

Thanks
Re: replacing certain characters in string [message #190845 is a reply to message #190844] Fri, 01 September 2006 09:51 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
SELECT translate(yourstring,'.,','__') from yourtable;
Re: replacing certain characters in string [message #190847 is a reply to message #190844] Fri, 01 September 2006 09:56 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
In 10g you can use REGEXP_REPLACE:
SQL> SELECT regexp_replace('a,b,c.d','[.,]','_') x
  2  FROM   dual
  3  /

X
-------
a_b_c_d

SQL>


Or translate in earlier versions:
SQL> SELECT translate('a,b,c.d','.,','__') x
  2  FROM   dual
  3  /

X
-------
a_b_c_d

SQL>


MHE
Re: replacing certain characters in string [message #190848 is a reply to message #190845] Fri, 01 September 2006 10:00 Go to previous message
123soleil
Messages: 35
Registered: July 2006
Member
JRowbottom wrote on Fri, 01 September 2006 09:51

SELECT translate(yourstring,'.,','__') from yourtable;



Looks like a nice solution, but doesn't seem to work????

edit: sorry, thought that '__' (2x '_') was a typo. Works great, thanks!

[Updated on: Fri, 01 September 2006 10:03]

Report message to a moderator

Previous Topic: Help with CASE statement
Next Topic: URGENT!! URGENT!!
Goto Forum:
  


Current Time: Sat Dec 10 02:59:58 CST 2016

Total time taken to generate the page: 0.22528 seconds