Home » SQL & PL/SQL » SQL & PL/SQL » Removal of special characters
icon4.gif  Removal of special characters [message #186288] Mon, 07 August 2006 05:45 Go to next message
kaushikmradul
Messages: 2
Registered: August 2006
Location: India
Junior Member
Hi,

This is a peculiar problem that I have faced. There are special characters in the table that I am working on. The special characters ranges from ASCII 1 to ASCII 22. Now the problem is that I need to remove these special characters when SELECTing data from this particular table. I tried using REPLACE and DECODE but none worked.
Occurance of this special character can be any where in the "description" field. I tried using REPLACE, but the problem is that there are 22 such special characters in the ASCII chart, so I need to write 22 such statements.
Please help....!!!

Thanks,
MK
Re: Removal of special characters [message #186291 is a reply to message #186288] Mon, 07 August 2006 05:58 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'd have a look at Translate if I were you.
Re: Removal of special characters [message #186387 is a reply to message #186291] Mon, 07 August 2006 14:59 Go to previous messageGo to next message
kaushikmradul
Messages: 2
Registered: August 2006
Location: India
Junior Member
thats correct, but problem is that I have 32 special characters. Ranging from 1 to 31...so would TRANSLATE work....as some of the special charaters are not visible...like newline....
Re: Removal of special characters [message #186396 is a reply to message #186387] Mon, 07 August 2006 15:43 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You could try with CHR() function instead of typing the "real" (invisible) characters; something like this:
SQL> select translate ('ABC', chr(65), 'x') from dual;

TRA
---
xBC

SQL>
Re: Removal of special characters [message #186458 is a reply to message #186396] Tue, 08 August 2006 01:13 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
A quick search showed me this solution from Art Metzer. Tom Kyte also has addressed this issue.

MHE
Previous Topic: What is the query to get the 4th last maximum salary
Next Topic: Execute Immediate
Goto Forum:
  


Current Time: Sat Dec 03 22:24:27 CST 2016

Total time taken to generate the page: 0.11956 seconds