Numeric and Non Numeric Data
449944Aug 11 2005 — edited Oct 16 2009Hi,
I have to check the data in Varchar2 field idtr and if it is numeric some actions are to be done on the data and if it is numeric some other different action is to be done.
So I used some logic for finding whether the data is numeric or non numeric which I got it from the net.
Now my problem is all decimal numbers are to be treated as numeric but this logic is treating decimals as non numeric and I am not able to proceed further.
Can some one help me out in this issue.
Below is the Logic and actions I am doing
select pol_id,lpad(substr(trunc(to_number(idtr),0),1,10),10,0),idtr
from pl_insrd
where length(idtr) - length(translate(idtr,chr(1)||translate(idtr,CHR(1)||'1234567890',CHR(1) ),CHR(1) ) ) = 0
and length(idtr) > 9
UNION
select pol_id,lpad(trunc(idtr,0),10,0),idtr
from pl_insrd
where length(idtr) - length(translate(idtr,chr(1)||translate(idtr,CHR(1)||'1234567890',CHR(1) ),CHR(1) ) ) = 0
and length(idtr) < 10
UNION
select pol_id,substr(idtr,1,10),idtr
from pl_insrd
where length(idtr) - Length(TRANSLATE(idtr,CHR(1)||TRANSLATE(idtr,CHR(1)||'1234567890', CHR(1) ), CHR(1) ) ) > 0
and length(idtr) > 9
UNION
select pol_id,lpad(idtr,10,0),idtr
from pl_insrd
where length(idtr) - Length(TRANSLATE(idtr,CHR(1)||TRANSLATE(idtr,CHR(1)||'1234567890', CHR(1) ), CHR(1) ) ) > 0
and length(idtr) < 10