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;
EXAMPLE:
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'?
โปรโมชั่น pg slot มากมาย เล่นง่ายจ่ายจริง แตกจริง ต้อง pg slot เท่านั้น! เล่นสล็อต พีจีสล็อต เว็บไซต์ตรงผู้ให้บริการเกมสล็อตออนไลน์ชั้นหนึ่ง ทกลอง เล่น ฟรี พร้อมโบนัส
ReplyDelete