0

I have a string like '11dd$%e11!@h'

Can I have a regular expression to remove only special character from the above string.

Character and number should be there after special character elimination.

another requirement if string is '@#45gr@@3. query should give me '@#@@''

Debasis Mohapatra
  • 129
  • 3
  • 16
  • By "character and number" do you mean "letter and number"? (And, careful with "letter" - do you mean letter from the English alphabet, as in simple ASCII characters, as opposed to Arabic or Chinese of Korean characters?) –  Feb 22 '18 at 05:52
  • & should handeled.If & is there inside the string it is asking for value.How to handle that? – Debasis Mohapatra Feb 22 '18 at 05:54
  • @DebasisMohapatra - that is only true if you are doing it interactively. In an interactive session, issue the command `SET DEFINE OFF` to turn off the special meaning of ampersand. –  Feb 22 '18 at 06:02

1 Answers1

1
select '11dd$%e11!@h' as input, regexp_replace('11dd$%e11!@h', '[^[:alnum:]]') as output
from   dual
;

INPUT         OUTPUT 
------------  --------
11dd$%e11!@h  11dde11h

[:alnum:] is shorthand for all letters (standard ASCII letters, lower and upper case) and all digits. [^ ... ] means everything EXCEPT .... So, this will replace everything EXCEPT letters and digits with... nothing (since we didn't give a third argument to REGEXP_REPLACE).

EDIT: The OP added a second part to the question.

If the assignment is to remove all the alpha-numeric characters ONLY, and to keep everything else, simply remove the ^ from the regular expression.

select '11dd$%e11!@h' as input, regexp_replace('11dd$%e11!@h', '[[:alnum:]]') as output
from   dual
;

INPUT         OUTPUT
------------  ------
11dd$%e11!@h  $%!@
  • how to display only special character.My requirement is both in different places of code – Debasis Mohapatra Feb 22 '18 at 05:56
  • @DebasisMohapatra - if you understand how the solution above removes all the non-alphanumeric characters, it should be very easy to "guess" how to do the opposite. The hint is in the meaning of the character `^` in the regular expression. –  Feb 22 '18 at 05:58
  • Telling the OP to guess how to change your answer to get the solution they need is less helpful than just correcting your code. All that happened was that the OP asked the same question in a new thread, thus wasting more people's time. – APC Feb 22 '18 at 08:17
  • @APC - The OP asked how to **remove** special characters from the input string. I took the Comment below my Answer to mean that he has a RELATED question, not that he doesn't understand the meaning of "remove". If you read the question (without the example, which was added **after** I answered the Comment), you will see that there is nothing to correct in my code. If the OP then changed the question, adding the example showing that "remove" in his mind means "keep only", I can't do much about that. –  Feb 22 '18 at 13:55
  • Fair enough. I admit I didn't check the edit history of the question body. – APC Feb 22 '18 at 14:02