Removing Special characters from a field in a table

[anjana.g]

Hi All,

Could anyone of you please help me out in developing a query.

My requirement is: The pl/sql code should search for special characters in the columns in a table and if it finds any that should be deleted and rest of the text should be again inserted( without that spl char)

For eg: table name : temp
columns are : name, addr1,addr2,addr3
let the name be kee$@rthi. The script should remove $@ and update the name field with keerthi.
suggest me how this can be done using loop and substr?

Please post your thoughts on this.

[PAVB]

...and, so far what do you have?

[anjana.g]

Could you please be precise?

[culonbu]

try TRANSLATE() function

[mallangouda]

Hi Anjana,
Using translate function you can solve your problem.
See for example in your case (presently) only two special characters are present, so in translate add those special characters.

update test1 set name = ( select translate(name,'a$@','a') from temp)