RE: RE : package to check if the input chars are number

From: Channa, Santhosh, SITS <schanna_at_att.com>
Date: Fri, 7 Jul 2000 15:55:15 -0400
Message-Id: <10551.111477@fatcity.com>

Try this:

declare
x varchar2(10);
begin
select to_number('1asdf23') into x from dual; dbms_output.put_line('valid character'); exception
when invalid_number then dbms_output.put_line('invalid character'); end;

Good Luck.

Santhosh Babu

Work: 336 698 2377
Home: 336 294 8076
Fax : 336 698 2385

 <<...OLE_Obj...>>

> ----------
> From: Stephane Faroult[SMTP:sfaroult_at_oriolecorp.com]
> Sent: Friday, July 07, 2000 5:13 AM
> To: Multiple recipients of list ORACLE-L
> Subject: RE : package to check if the input chars are number
>
> Grace,
>
> This is pretty easy to do even with SQL (make it a package if you
> want) with the help of a couple of functions :
>
> SQL> select nvl(length(rtrim(translate('123', '123456789', '000000000'),
> '0')), 0)
> 2 from dual
> 3 /
>
> NVL(LENGTH(RTRIM(TRANSLATE('123','123456789','000000000'),'0')),0)
> -------------------------------------------------------------------
> 0
>
> 1 row selected.
>
> SQL> select nvl(length(rtrim(translate('2RY', '123456789', '000000000'),
> '0')), 0)
> 2 from dual
> 3 /
>
> NVL(LENGTH(RTRIM(TRANSLATE('2RY','123456789','000000000'),'0')),0)
> -------------------------------------------------------------------
> 3
>
> 1 row selected.
>
>
> (you can also add a decimal point and a minus thing to the list of
> digits, if you are not only dealing with positive integers). If the
> result is 0, then it's a number.
>
> --
> Regards,
>
> Stephane Faroult
> email: sfaroult_at_oriolecorp.com
> Oriole Corporation
> Voice: +44 (0) 7050-696-269
> Fax: +44 (0) 7050-696-449
> Performance Tools & Free Scripts
> ------------------------------------------------------------------
> http://www.oriolecorp.com, designed by Oracle DBAs for Oracle DBAs
> ------------------------------------------------------------------
>
> >
> > gurus,
> >
> > is there a package that can check if the input is a numeric or varchar
> when
> > the variable has a varchar2 type?
> >
> > e.g. user input '123' === output is numeric
> > '2rY' === output not numeric
> >
> > thanks
> >
> >
> >
> > Grace Lim
> > Suy Sing Comm'l Corp.
> > 247-41-34
> > --
> > Author: grace lim
> > INET: mglim_at_softhome.net
> >
> --
> Author: Stephane Faroult
> INET: sfaroult_at_oriolecorp.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
Received on Fri Jul 07 2000 - 14:55:15 CDT