Home » SQL & PL/SQL » SQL & PL/SQL » Handling ASCII values in ORACLE like ^M
Handling ASCII values in ORACLE like ^M [message #18766] Wed, 13 February 2002 14:16 Go to next message
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 #18786 is a reply to message #18766] Thu, 14 February 2002 00:42 Go to previous messageGo to next message
pratap kumar tripathy
Messages: 660
Registered: January 2002
Senior Member
Will it be ok if I use CHAR instead of Varchar2.!!??--->no

if u want to get rid of ^M then replace it with blank or null.

otherwise u can write pre-insert/pre-update trigger which will replace ^M.
Re: Handling ASCII values in ORACLE like ^M [message #18795 is a reply to message #18786] Thu, 14 February 2002 07:20 Go to previous message
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)...)
Previous Topic: buffer
Next Topic: Explain plan
Goto Forum:
  


Current Time: Thu Sep 23 11:57:37 CDT 2021