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?

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.

 

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

    

    

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

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

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.

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]*$');

Hello,

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

Cheers

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