Home » SQL & PL/SQL » SQL & PL/SQL » remove special characters except , - . (oracle 10g)
remove special characters except , - . [message #614316] Tue, 20 May 2014 15:14 Go to next message
thelearner
Messages: 133
Registered: April 2013
Location: INDIA
Senior Member
Hi I want to remove all special characters except , . -


Example:

Given string
Test!23,$3 23.string)125_-`~

Required output

Test23,3 23.string125-

please suggest me
Re: remove special characters except , - . [message #614317 is a reply to message #614316] Tue, 20 May 2014 15:17 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://docs.oracle.com/cd/E16655_01/server.121/e17209/functions226.htm#SQLRF06145
Re: remove special characters except , - . [message #614353 is a reply to message #614316] Wed, 21 May 2014 02:34 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
You can also use REGEXP_REPLACE with [:punct:] character class. You need to negate ",.-" from it.

[Updated on: Wed, 21 May 2014 02:36]

Report message to a moderator

Re: remove special characters except , - . [message #614362 is a reply to message #614353] Wed, 21 May 2014 03:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
You need to negate ",.-" from it.


What does this mean? Can you show us?

(Note I think TRANSLATE is a far better solution in this case.)


Re: remove special characters except , - . [message #614372 is a reply to message #614362] Wed, 21 May 2014 04:07 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

An answer, Lalit?
We are waiting you show us.

Re: remove special characters except , - . [message #614380 is a reply to message #614362] Wed, 21 May 2014 04:23 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Michel Cadot wrote on Wed, 21 May 2014 13:47

Quote:
You need to negate ",.-" from it.


What does this mean? Can you show us?


Michel, updated comment got messed up, entire comment did not get posted. Sorry for that Sad

I meant that, regexp_replace could also be used. And, [:punct:] character class would handle all punctuation characters together. But if requirement is just to avoid few characters, then put them into a negate class.

SQL> SELECT REGEXP_REPLACE(q'[Test!23,$3 23.string)125_-`~]', '[^a-zA-Z0-9, .-]') STR
  2    FROM DUAL;
STR
----------------------
Test23,3 23.string125-


Just another way.
Re: remove special characters except , - . [message #614382 is a reply to message #614380] Wed, 21 May 2014 04:33 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

This was far from clear in your post which said "You can also use REGEXP_REPLACE with [:punct:] character class" but your example does not show this class. So you meant you could use it but actually you can't. Smile

[Updated on: Wed, 21 May 2014 04:51]

Report message to a moderator

Re: remove special characters except , - . [message #614398 is a reply to message #614362] Wed, 21 May 2014 05:51 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Michel Cadot wrote on Wed, 21 May 2014 13:47

(Note I think TRANSLATE is a far better solution in this case.)


But for such simple requirement, regexp would be much simpler to code. Unless, we are concerned about the cpu consumption and other performance aspects for a complex task. Any reason that you consider translate being far better solution in this case?
Re: remove special characters except , - . [message #614399 is a reply to message #614398] Wed, 21 May 2014 06:12 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
But for such simple requirement, regexp would be much simpler to code.


Regexp are always more complex and less safe to maintain than a list of allowed characters.

Quote:
Unless, we are concerned about the cpu consumption and other performance aspects for a complex task.


Indeed I am; however repexp functions have been greatly improved in 11g compared to 10g but still less efficient than TRANSLATE.

Previous Topic: Reset Sunday as first start week
Next Topic: Retain first and last record according to id.
Goto Forum:
  


Current Time: Thu Mar 28 12:09:14 CDT 2024