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

> --