Is there any function in oracle to check whether a value is numeric or not. I have a column which contains both numeric and alphanumeric data. I want to fetch only the numeric data from that column. Can anyone tell me how to do it?

Krishnakant Mahamuni

• Sep 14th, 2006

 

Hi Anu,

There does not exists any build in function which checks for a numeric value instead you can write your own function as follows.

CREATE OR REPLACE FUNCTION Isnumber(p_num VARCHAR2) RETURN NUMBER
AS
 a NUMBER;
BEGIN
 a := p_num;
 RETURN 1;
EXCEPTION WHEN OTHERS THEN
 RETURN 0;
END;
/

This function can now be called in a SQL query which will retum 1 if the value passwd is numeric else it will return 0.

Thanks & Regards,
Krishnakant.

 

Anu

• Sep 15th, 2006

 

Hi,

    Thank you so much krishnakant..

      You can also get this done using the following query.     

        select * from test where translate(a,'_0123456789','_') is null;

Regards,

Anu

    

    

Sushil

• Feb 21st, 2007

 

in SELECT clause - use with decode
decode(
regexp_instr(substr ( rtrim(Objects.project_name), -9, 9),'[A-Z]|[a-z]'), 0,substr ( rtrim(Objects.project_name), -9, 9))  

in WHERE clause
regexp_instr(substr ( rtrim(Objects.project_name), -9, 9),'[A-Z]|[a-z]') = 0

amarnathtnl

 Profile Answers by amarnathtnl 

• Jun 29th, 2007

 

hi anu,


we can use the following querry to get the answer

select * from where upper(column_name)=lower(column_name);


here colum_name is name of the column which contains both numeric and
alpha- numeric values.

THE QUERRY WORKS LIKE THIS--

When ever we compare two values,  oracle compares them by  their ASCII VALUES.

 ASCII(UPPER(char)) is not equal to ASCII(LOWER(char))
 but for numbers
upper(number) is always equal to lower(number)

if u have any querries on this u can send to my mail amar dot tnl at gmail dot com

jryan999

 Profile Answers by jryan999 Questions by jryan999

• May 1st, 2008

 

Things to consider if performance is a worry:-

1.  Executing Stored Functions from within a SQL statement include a signficant overhead.  If you can execute code inline (eg. in there where clause using Oracle provided functions) they will always run faster than if built into a user written function.

2.  I tested two solutions on a Pentium Dual Core 2Ghz on Oracle 10g.  The "translate" solution and the "Exception Handling from a to_number".  Against 500,000 rows:-

• Exception solution processed 125,000 rows per second
• Translate solution processed 43,000 rows per second

ie. Exception solution (the first proposed) is about 3 times faster.  However, the Exception handling solution cannot be executed directly from SQL, but the Translate can be included in a SQL statement.

If this is done, the Translate solution produces:-

• Translate (inline) 333,000 rows per second

Naturally, you'd get better performance if you called the functions from within PL/SQL (assuming you'd bulk loaded the entries in memory), but it goes to show, the overhead produced by SQL -> PL/SQL calls for user written functions.

Note:  This overhead is nowhere near as bad for built in Oracle functions.

hifake

 Profile Answers by hifake 

• Jan 13th, 2010

 

REGEXP_LIKE can be used to match numbers
SELECT Fieldname1 FROM componentid WHERE REGEXP_LIKE (Fieldname1, '^[0-9]*$');

REGEXP_LIKE can be used to match alphabets only (alphanumeric and numbers ingnored)
SELECT Fieldname1 FROM componentid WHERE REGEXP_LIKE (Fieldname1, '^[a-zA-Z]*$');

me

• Jul 3rd, 2012

 

Hello,

Using the upper/lower functions is faster than the regular expression approach.

Cheers

Breta

• Aug 28th, 2013

 

It is faster, but do not solve case when value is "123 456" which is not numeric, but will pass your condition.
Cheers

Sameer Shinde

• Mar 2nd, 2016

 

Code
-- Check the given value is numeric or not


DECLARE


    i_input VARCHAR2(100):=123; --Change it accordingly


BEGIN


    IF RTRIM(i_input,0123456789) IS NULL THEN


        DBMS_OUTPUT.PUT_LINE(i_input ||  - NUMBER format);


    ELSE


        DBMS_OUTPUT.PUT_LINE(i_input ||  - Alpha Numeric format);


    END IF;


END;

Pradeep

• Mar 4th, 2016

 

Best way is to use Translate.
Translate - Will do Char by Char replacement.
SELECT col, TRANSLATE(col,*ABCDEFGHIJKLMNOPQRSTUVWXYZ,*) FROM pd_test