Home » SQL & PL/SQL » SQL & PL/SQL » special character handling in translate query
special character handling in translate query [message #244269] Tue, 12 June 2007 03:54 Go to next message
SWATI15.U
Messages: 6
Registered: June 2007
Location: DELHI
Junior Member

i have a column which is varchar2,i store both numeric and char data in this now i am using translate and replace fuction to seperate the numeric and char value,but if i have a data emp_code,it gives error fir '_'.it will print empcode.how can i find out the same output.can anyone help.
Re: special character handling in translate query [message #244276 is a reply to message #244269] Tue, 12 June 2007 04:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please always post your Oracle version (4 decimals).
Please read and follow How to format your posts.

Post an example of what you have, what you do, what you get, what you want.

Regards
Michel
Re: special character handling in translate query [message #244279 is a reply to message #244276] Tue, 12 June 2007 04:09 Go to previous messageGo to next message
sanka_yanka
Messages: 184
Registered: October 2005
Location: Kolkata
Senior Member

Use this query

select replace(trim(translate('xyzSS0124','abcdefghijklmnopqrstuvxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',' 0123456789')),'_','') as only_number,
replace(trim(translate('xyzSS124','abcdefghijklmnopqrstuvxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789','abcdefghijklmnopqrstuvxyzABCDEFGHI JKLMNOPQRSTUVWXYZ ')),'_','') as only_char
from dual
Re: special character handling in translate query [message #244291 is a reply to message #244269] Tue, 12 June 2007 04:50 Go to previous messageGo to next message
sanka_yanka
Messages: 184
Registered: October 2005
Location: Kolkata
Senior Member

If you are using 10g then you can use

SELECT REGEXP_REPLACE(YOURSTRING_VALUE,'[^[:alpha:]]','') as char_only,REGEXP_REPLACE(YOURSTRING_VALUE,'[^[:digit:]]','') as number_only
FROM DUAL;
Re: special character handling in translate query [message #244322 is a reply to message #244279] Tue, 12 June 2007 05:52 Go to previous messageGo to next message
SWATI15.U
Messages: 6
Registered: June 2007
Location: DELHI
Junior Member

let me explain you my query,
select replace(translate(field_name,'_0123456789','00000000000'),'0')VALUE_NAME,
to_number(replace(translate(lower(field_name),'_abcdefghijklmnopqrstuvwxyz',rpad('z',27,'z')),'z'))VALUE_NO from table_name
but while using this query if filed name contain any special char it gives an error.e.g if field name is emp_code then it will give error if i'll remove '_' then it will work ok.how to solve it.
Re: special character handling in translate query [message #244325 is a reply to message #244322] Tue, 12 June 2007 06:04 Go to previous messageGo to next message
sanka_yanka
Messages: 184
Registered: October 2005
Location: Kolkata
Senior Member

Your query have no problem. I've checked it.
By the way what is your oracle database version.
Re: special character handling in translate query [message #244333 is a reply to message #244325] Tue, 12 June 2007 06:37 Go to previous message
SWATI15.U
Messages: 6
Registered: June 2007
Location: DELHI
Junior Member

oracle 9i.actually in this query it will replace '_',it will give error if field name contain '_',error is wrong number.but in output i want '_'.
Previous Topic: system name
Next Topic: Help with PL/SQL procedures and functions
Goto Forum:
  


Current Time: Sat Dec 10 16:26:23 CST 2016

Total time taken to generate the page: 0.04224 seconds