CREATE OR REPLACE FUNCTION ISNUMERIC (PARAM IN CHAR) RETURN NUMBER AS

DUMMY VARCHAR2(100);

BEGIN

DUMMY:=TO_CHAR(TO_NUMBER(PARAM));

RETURN (1);

EXCEPTION

WHEN OTHERS THEN

RETURN (0);

END;

SELECT * FROM EMP WHERE ISNUMERIC(PHONE) = 1;

DUMMY VARCHAR2(100);

BEGIN

DUMMY:=TO_CHAR(TO_NUMBER(PARAM));

RETURN (1);

EXCEPTION

WHEN OTHERS THEN

RETURN (0);

END;

**EXAMPLE:**SELECT * FROM EMP WHERE ISNUMERIC(PHONE) = 1;

**Related Post:**

- ORA-06502: PL/SQL: numeric or value errorstring

- Oracle: Extract Numbers from String

thats pretty bad practice to use exception handling to determine something that can be figured out by calculation. Mainly becuase exception handling invokes an extra performance overhead. This function below works about twice as fast.

ReplyDeleteLENGTH(TRIM(TRANSLATE(myfield, ' +-.0123456789', ' '))) is not null

Please check your code is not working, it is saying that 'A1B23C456' is numeric

Deletewith t as

(

select 'A1B23C456' myfield from dual

)

select myfield from t

where LENGTH(TRIM(TRANSLATE(myfield, ' +-.0123456789', ' '))) is not null

Please note: here we are checking that is the field has the value which which can be converted to number.

Replace the last parameter with 13 (0 through 9, + - and period) blanks within quotes

DeleteNimish Garg, you have misinterpreted this very simple code fragment. The code you have is selecting rows that are NOT numeric - you add a space for every field that is not numeric. So in your example you are looking for values that are Not numeric. If you want values that ARE numeric, simply change your 'Is Not Null' to 'Is Null'

ReplyDeleteClearly this is exactly what I call a terrific article! Do you use this domain for your private joy exclusively or you actually have it to get profit with its help?

ReplyDeleteGOOD TIP

ReplyDeletewith t as

ReplyDelete(

select 'A1B23C456' myfield from dual

)

select myfield from t

where LENGTH(TRIM(TRANSLATE(myfield, ' +-.0123456789', ' '))) is not null actually this is working. But to find numeric values you should check is null.

The bad thing with this type of "parsing" data to be numeric is that the following will also result in "null" which means "numeric":

ReplyDeleteselect LENGTH(TRIM(TRANSLATE ('-.+56', ' +-.0123456789',' '))) from dual;

And spaces are also considered numerical....

ReplyDeletenot by this code

DeleteFor more thorough checking (i.e. '+123.4' is numeric, '+123..4' is non numeric):

ReplyDeleteSELECT DECODE(TRANSLATE(DECODE(INSTR(:VAL,'.',1,2),0,

DECODE(INSTR(:VAL,'+',1,2),0,

DECODE(INSTR(:VAL,'-',1,2),0,:VAL

,'X')

,'X')

,'X'),'X+-0123456789.','X'),NULL

,DECODE(LENGTH(TRANSLATE(:VAL,'+-0123456789.','+-')),2,'NON_NUMERIC','NUMERIC'),'NON_NUMERIC') NUMERIC_VALUE

FROM DUAL;

it say that 1+ and + and - is numeric. I assume correct is:

DeleteSELECT

case

when instr(trim(:VAL), '+') > 1 then 'NON_NUMERIC'

when instr(trim(:VAL), '-') > 1 then 'NON_NUMERIC'

else DECODE(

TRANSLATE(DECODE(INSTR(:VAL,'.',1,2),0,

DECODE(INSTR(:VAL,'+',1,2),0,

DECODE(INSTR(:VAL,'-',1,2),0, :VAL, 'X'), 'X'),'X'),

'X+-0123456789.','X'),NULL,

DECODE(LENGTH(TRANSLATE(:VAL,'+-0123456789.','+-')),2,'NON_NUMERIC','NUMERIC'),'NON_NUMERIC')

end NUMERIC_VALUE

FROM DUAL;

create function isnumeric (testStr in varchar2) return boolean

ReplyDeleteis

begin

return testStr is not null and regexp_like(testStr,'^-?[0-9]*\.?[0-9]*$');

end;

It is a bad idea that using "LENGTH(TRIM(TRANSLATE(myfield, ' +-.0123456789', ' '))) is not null" to judge a string is a number or not.

ReplyDeleteMay I ask what will happen when myfield is some strings like '123-456' or '99.99.99'?