CODEWITHSUNDEEP
sqloracle-database
Radhika
Radhika

Reputation: 23

Remove special characters from a string in oracle sql

The field contains special characters like pipe,returns ,* and ( example: 

Table Name: Employee
Field names: id, name
ID   Name
01   Radhika N (MBA)*MCA*
02   Venu (BBA)
03   (MBA)Ananth
04   Visal **MCA**

Now i want a select statement that removes all special characters and my result should be 

ID   Name
01  RADHIKA N
02  VENU
03  ANANTH
04  VISHAL

Select id, upper(replace(replace(replace(replace(replace(replace(name,'|',' '),chr(10),' '),chr(13),' '),chr(9),' '), chr(42), ' '), chr(40), ' ')) as NAME
from employee

this will take out any ascii special characters and * But the result was:

ID   Name
01  RADHIKA N (MBA) MCA
02  VENU (BBA)
03  (MBA) ANANTH
04  VISHAL MCA

How do i remove "(MBA)" from the names?

Upvotes: 0

Views: 18897

Answers (2)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

For the sample data shown, the query below would work.

select trim(upper(regexp_replace(name,'[\(|\*](.*)[\)|\*]','')))
from tbl

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269483

If I understand correctly, you can use regexp_replace(). I would approach this in two steps:

select regexp_replace(regexp_replace(name, '[(][A-Z]+[)]', ''), '[^a-zA-Z ]', '')

The first removes the part in parentheses. The second keeps only alpha-numeric values.

Upvotes: 0

Related Questions