Re: ** numeric check

If the data is as simple as the examples you posted, then the following would do the trick:

select c1, decode(translate(c1,'a0123456789','a'),null,'numeric','alpha') from t1

C1 DECODE(
---------- -------

12345      numeric
TEST       alpha
T12345     alpha
12345      numeric

If you have to account for negatives, decimal point, scientific notations, etc. then you're better off using to_number and trapping the exception as Tanel suggested.

Quoting Tanel Põder <tanel.poder.003_at_mail.ee>:

> Hi!
>
> You could try to use TO_NUMBER function on the string and catch the
> possible
> exception, returning an error code if unsuccessful. An example I've found
> from internet:
>
>
> > Hi,
> > I have a varchar2 column which contains some text and some totally
> numeric values. Is there an easy way to check if the value is totally
> numeric instead of looking at ascii values etc. Thanks for your help.
> >

-- 
Regards

Wolfgang Breitling
Oracle 7,8,8i,9i OCP DBA
Centrex Consulting Corporation
www.centrexcc.com

----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------