OraFAQ Forum: SQL & PL/SQL » special character handling in translate query

Home » SQL & PL/SQL » SQL & PL/SQL » special character handling in translate query

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

special character handling in translate query [message #244269]

Tue, 12 June 2007 03:54

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.

Report message to a moderator

Re: special character handling in translate query [message #244276 is a reply to message #244269]

Tue, 12 June 2007 04:04

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: special character handling in translate query [message #244279 is a reply to message #244276]

Tue, 12 June 2007 04:09

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

Report message to a moderator

Re: special character handling in translate query [message #244291 is a reply to message #244269]

Tue, 12 June 2007 04:50

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;

Report message to a moderator

Re: special character handling in translate query [message #244322 is a reply to message #244279]

Tue, 12 June 2007 05:52

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.

Report message to a moderator

Re: special character handling in translate query [message #244325 is a reply to message #244322]

Tue, 12 June 2007 06:04

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.

Report message to a moderator

Re: special character handling in translate query [message #244333 is a reply to message #244325]

Tue, 12 June 2007 06:37

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 '_'.

Report message to a moderator

Previous Topic:	system name
Next Topic:	Help with PL/SQL procedures and functions

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Feb 11 08:40:40 CST 2025