Home » SQL & PL/SQL » SQL & PL/SQL » translate (Oracle 10g)
translate [message #601883] Tue, 26 November 2013 11:52 Go to next message
m.abdulhaq
Messages: 125
Registered: April 2013
Location: Ajman
Senior Member
Dear experts,
I have one table where user data from excel will get uploaded , i am actually make a check for one column sld_flex_01, the data can come into these field as three different values from excel.It can be 'NA', or it can be a whole number or number with precision.if th



create table ot_trans ( SL_NO NUMBER,sld_flex_01 varchar2(40) );

insert into ot_trans (SL_NO,sld_flex_01) values (1,'NA');

insert into ot_trans (SL_NO,sld_flex_01) values (2,'333');

insert into ot_trans (SL_NO,sld_flex_01) values (3,'3.3202408');

SELECT TRANSLATE(SLD_FLEX_01,'0123456789',' ') FROM OT_TRANS;

TRANSLATE(SLD_FLEX_01,'0123456789','')
NA

.  

now i am getting the result as

select SL_NO,decode(TRANSLATE(SLD_FLEX_01,'0123456789',' ') ,' ',1,2) CHK from OT_TRANS;

SL_NO	CHK
1	2
2	2
3	2


what i want is .

select SL_NO,decode(TRANSLATE(SLD_FLEX_01,'0123456789',' ') ,' ',1,2) CHK from OT_TRANS;

SL_NO	CHK
1	2
2	1    ---since this is matching with null it should be 1 but why its comming as 2.
3	2




Thank you and Best regards

[Updated on: Tue, 26 November 2013 12:21] by Moderator

Report message to a moderator

Re: translate [message #601887 is a reply to message #601883] Tue, 26 November 2013 12:20 Go to previous messageGo to next message
Michel Cadot
Messages: 59296
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select SL_NO,decode(TRANSLATE(SLD_FLEX_01,' 0123456789',' ') ,null,1,2) CHK from OT_TRANS;
     SL_NO        CHK
---------- ----------
         1          2
         2          1
         3          2

3 rows selected.
Re: translate [message #601888 is a reply to message #601883] Tue, 26 November 2013 12:21 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2411
Registered: May 2013
Location: World Wide on the Web
Senior Member
m.abdulhaq wrote on Tue, 26 November 2013 23:22
since this is matching with null it should be 1 but why its comming as 2.


Since it is not a null.
Re: translate [message #602118 is a reply to message #601887] Fri, 29 November 2013 11:28 Go to previous message
m.abdulhaq
Messages: 125
Registered: April 2013
Location: Ajman
Senior Member
Thanks all of you for the help.I got it now.
Previous Topic: Fetching results using intermediate table
Next Topic: not able to execute the explain plan in development in TOAD
Goto Forum:
  


Current Time: Thu Oct 02 08:36:30 CDT 2014

Total time taken to generate the page: 0.10467 seconds