Re: package to check if the input chars are number

From: Rod Corderey <RodCorderey_at_Lane-Associates.com>
Date: Fri, 07 Jul 2000 05:14:28 +0100
Message-Id: <10550.111389@fatcity.com>

Hi
the following is a simplistic function depending on what you consider a number to be. You can change the format mask accordingly, but the number should be an Oracle number data type to allow implicit conversion when presenting a number to the function.

The 'A' in the format is just to ensure that all subsequent members of the format are discarded from the input string.  

create or replace function is_a_number ( v_string varchar2) return varchar2
is
begin

 if nvl(length(translate(upper(v_string),

                             'A0123456789.-','*')
                        ),0) =  0 then
	return('Y');
 else
	return('N');

 end if;

end;

SQL> select is_a_number('This is A StrIng + 67.9') from dual; IS_A_NUMBER('THISISASTRING+67.9')

---

N

SQL> select is_a_number('785.934') from dual; IS_A_NUMBER('785.934')

---

Y

SQL> select is_a_number(754.34) from dual; IS_A_NUMBER(754.34)

---

Y

SQL> select is_a_number(-0.4568) from dual; IS_A_NUMBER(-0.4568)

---

Y  

hope it helps

Rod

-- 
Rod Corderey

Lane Associates
RodCorderey_at_Lane-Associates.com
http://www.Lane-Associates.com

grace lim wrote:


> 

> 

> 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

> 

> 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 Thu Jul 06 2000 - 23:14:28 CDT