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?

Questions by anu_dw   answers by anu_dw

Showing Answers 1 - 8 of 8 Answers

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.

 

  Was this answer useful?  Yes

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

    

    

  Was this answer useful?  Yes

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

  Was this answer useful?  Yes

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

  Was this answer useful?  Yes

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.

  Was this answer useful?  Yes

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

  Was this answer useful?  Yes

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

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

Answer Question

Click here to Login / Register your free account


 
Send   Reset

 

Related Answered Questions

 

Related Open Questions