Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE : package to check if the input chars are number

RE : package to check if the input chars are number

From: Stephane Faroult <sfaroult_at_oriolecorp.com>
Date: Fri, 07 Jul 2000 09:57:32 +0200
Message-Id: <10551.111407@fatcity.com>


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
> --
Received on Fri Jul 07 2000 - 02:57:32 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US