CODEWITHSUNDEEP
sqlregexoracle-database
Annapurna
Annapurna

Reputation: 81

how remove special character and digits from a string but ignore white spaces

i tried to remove special characters and numeric number from string of sentence but it should ignore white spaces if there is a more than one it should replace with one

SQL developer,oracle 11g

select REGEXP_REPLACE ('Annapurna1@ Poojari675&^','(\W|\d)','') from dual;

actually output is AnnapurnaPoojari but i need as Annapurna Poojari

Upvotes: 0

Views: 362

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65228

You can alternatively use [^[:alpha:] ]+ pattern to remove non-alphabetic characters and keep spaces :

select regexp_replace('Annapurna1@ Poojari675&^','[^[:alpha:] ]','') as "Result String"
  from dual;

Result String
-----------------
Annapurna Poojari

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269753

You can be more explicit about the characters you want to keep:

select REGEXP_REPLACE('Annapurna1@ Poojari675&^', '([^a-zA-Z ])', '')
from dual;

Upvotes: 2

Related Questions