Removing Special characters from a field in a table
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.
...and, so far what do you have?
Pablo (Paul) Berzukov
Author of Understanding Database Administration
available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Could you please be precise?
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)
Click Here to Expand Forum to Full Width