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 decodedecode(regexp_instr(substr ( rtrim(Objects.project_name), -9, 9),'[A-Z]|[a-z]'), 0,substr ( rtrim(Objects.project_name), -9, 9))   in WHERE clauseregexp_instr(substr ( rtrim(Objects.project_name), -9, 9),'[A-Z]|[a-z]') = 0

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 secondTranslate solution processed 43,000 rows per secondie. 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 secondNaturally, 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 numbersSELECT 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