Remove special characters from a string in oracle sql

Question

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?

No, that wasn't the result. The letters between * ... * should still be there also, where did they go? Then: what is the assignment? You started with "remove special characters" (even in your title), but it seems your assignment is different. Surely M, B, A are not "special characters". Please formulate your problem more carefully / more meaningfully. — user5683823
– user5683823, Commented Nov 1, 2017 at 18:24
Yes, i have edited my question, the result had the letters between .... in the portion of (MBA) , M, B, A are not special characters but they are within the parenthesis ( ) which is a special character — Radhika
– Radhika, Commented Nov 1, 2017 at 18:33

Vamsi Prabhala · Accepted Answer · 2017-11-01 18:35:12Z

3

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

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

answered Nov 1, 2017 at 18:35

Vamsi Prabhala

49.4k4 gold badges41 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Radhika Over a year ago

Your suggestion helped. Thank you. I used; select id, upper(replace(replace(replace(replace(trim(regexp_replace(name,'[(|*](.*)[)|*]','')),'|',' '),chr(10),' '),chr(13),' '),chr(9),' ')) as name from employee this gave me the desired results. Thanks again.

Gordon Linoff · Accepted Answer · 2017-11-01 18:26:47Z

0

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.

answered Nov 1, 2017 at 18:26

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

Radhika Over a year ago

This resulted in : ID Name 01 RADHIKA N MBA) MCA 02 VENU BBA) 03 MBA) ANANTH 04 VISHAL MCA

Collectives™ on Stack Overflow

Remove special characters from a string in oracle sql

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related