remove special characters except , - . [message #614316] |
Tue, 20 May 2014 15:14 |
|
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 #614380 is a reply to message #614362] |
Wed, 21 May 2014 04:23 |
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
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 #614398 is a reply to message #614362] |
Wed, 21 May 2014 05:51 |
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 |
|
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.
|
|
|