Series: Subject: Topic:
Question: 249 of 429

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?

Asked by: anu_dw | Member Since Feb-2006 | Asked on: Sep 14th, 2006

View all questions by anu_dw   View all answers by anu_dw

Showing Answers 1 - 8 of 8 Answers
Krishnakant Mahamuni

Answered On : 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. 

Login to rate this answer.

Answered On : 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         

Login to rate this answer.

Answered On : Feb 21st, 2007

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

Login to rate this answer.

Answered On : Jun 29th, 2007

View all answers by amarnathtnl

Login to rate this answer.

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.

Login to rate this answer.

Answered On : Jan 13th, 2010

View all answers by hifake

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

Yes  1 User has rated as useful.
Login to rate this answer.

Answered On : Jul 3rd, 2012


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


Login to rate this answer.

Answered On : 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.

Login to rate this answer.

Give your answer:

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

Related Open Questions



twitter fb Linkedin GPlus RSS


Interview Question

 Ask Interview Question?


Latest Questions

Interview & Career Tips

Get invaluable Interview and Career Tips delivered directly to your inbox. Get your news alert set up today, Once you confirm your Email subscription, you will be able to download Job Inteview Questions Ebook . Please contact me if you there is any issue with the download.