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?
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]*$');
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
SELECT a.*FROM employee a WHERE rownum <=(SELECT trunc(count(*)/2)FROM employee)
minus
SELECT a.*FROM employee a WHERE rownum <>(SELECT trunc(count(*)/2)FROM employee)
sandeep
Dec 14th, 2017
What about special characters, this won't work as special characters don't have any upper or lower value.
Asish
Jan 7th, 2018
Here the where clause will be either Lower(fn) = upper(fn) Or Regexp_like(fn, ^[0-9]*$)
Dheeraj Singh
Sep 16th, 2018
You can try this one.I think this query can work.. Select * from tbl_regex Where REGEXP_LIKE(ENAME,^0123456789); Note:- tbl_regex is table name and ENAME is column name that contain numeric and string type data.
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
Related Answered Questions
Related Open Questions