| Handling ASCII values in ORACLE like ^M [message #18766] |
Wed, 13 February 2002 14:16  |
Bhavesh Shah
Messages: 1
Registered: February 2002
|
Junior Member |
|
|
Hi,
I see in my Oracle Table in a Varchar2 Field, Control Characters like ^M (when seen in unix after spooling) or CHR(13) when searched with SQL command as below...
select VarChar2FieldName from anyTable where VarChar2FieldName like '%'||chr(13)||'%';
The basic point is that in a business environment we only need valid ascii values which makes sense. If it has a value of ^M - this does not carry in any meaning for us and that too in a single line field varchar2. Please tell me more about how can we tackle this. !!??
Will it be ok if I use CHAR instead of Varchar2.!!??
It is very urgent. Hence, your kind co-operation and assitance is required.
Thank you and Best Regards
Bhavesh Shah
Email: bhavesh_amdocs@yahoo.com
|
|
|
|
|
|
| Re: Handling ASCII values in ORACLE like ^M [message #18795 is a reply to message #18786] |
Thu, 14 February 2002 07:20  |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
Tracnslate() can be used to eliminate characters.
select translate('AaBbCc'||chr(13)||'DE'||chr(0)||'FGH','a'||chr(13)||chr(0),'a') phone from dual;
if you want to eliminate a range of characters (say from 0 to 31 and 127 to 255) then it may be best to write a function to step through the whole string one character at a time to eliminate the ones outside of 32 to 126. The function will be slower than the translate function. you could also use the translate function inside your own function - because there will be lots of code if you need to represent each character...
translate(my_var, 'a'||chr(0)||chr(1)||chr(2)...)
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
