Re: Numeric Function

From: Norman Dunbar <Norman.Dunbar_at_lfs.co.uk>
Date: Thu, 8 Nov 2001 12:48:20 -0000
Message-ID: <E2F6A70FE45242488C865C3BC1245DA734F78C@lnewton.leeds.lfs.co.uk>

Hmmm. Works unless there are more than one decimal point in the input, as in '1.23.45' for example. :o(
Also, doesn't like 'e' notation as in '1e6' for 1,000,000 for example.

However, the following seems to work. Note that I've changed the return type for ease of testing :

CREATE OR REPLACE FUNCTION Is_Numeric(in_value IN VARCHAR2) RETURN INTEGER IS
      dummy NUMBER;
   BEGIN

      SELECT TO_NUMBER(in_value) INTO dummy FROM dual;
      RETURN 1;
   EXCEPTION
      WHEN OTHERS THEN
      RETURN 0;  

   END Is_Numeric;
/

Here are some tests :

SELECT Is_Numeric('123,456.01') FROM dual; 0

SELECT Is_Numeric('123456') FROM dual
1

SELECT Is_Numeric('1.23456') FROM dual
1

SELECT Is_Numeric('1.23.456') FROM dual
0

SELECT Is_Numeric('1e6') FROM dual
1

SELECT Is_Numeric('1.2e6') FROM dual
1

SELECT Is_Numeric('1.2.3e6') FROM dual
0

SELECT Is_Numeric('e2') FROM dual
0

SELECT Is_Numeric('e') FROM dual
0

Regards,
Norman.

---

---

Norman Dunbar			EMail:	Norman.Dunbar_at_LFS.co.uk
Database/Unix administrator	Phone:	0113 289 6265
				Fax:	0113 289 3146
Lynx Financial Systems Ltd.	URL:	http://www.Lynx-FS.com
------------------------------------------------------------------------

---

-----Original Message-----
From: markg_at_mymail.tm (MarkyG) [mailto:markg_at_mymail.tm] Posted At: Thursday, November 08, 2001 10:10 AM Posted To: server
Conversation: Numeric Function
Subject: Re: Numeric Function

The suggestions so far are fine but...

what about a number like 7.3?

You may want to include a decimal point in the translate list.

Try this. If you dont care about decimals, remove it from the translate list.

   FUNCTION Is_Numeric(in_value IN varchar2) RETURN boolean is

      v_retval boolean;
   BEGIN       IF REPLACE(TRANSLATE(in_value,'0123456789.',' '),' ','') IS NULL THEN

         v_retval := TRUE;
      ELSE
         v_retval := FALSE;
      END IF;

      RETURN v_retval;

   END Is_Numeric;

Mark

Richard Kuhler <noone_at_nowhere.com> wrote in message news:<%yjG7.20674$D5.8286434_at_typhoon.san.rr.com>...
> Connor McDonald wrote:
> >
> > NeedaHoliday wrote:
> > >
> > > Is there a function or method to search a string character and
find
> > > only numeric values?
> > >
> > > Thanks
> >
> > where
> > replace(
> > translate(col,'0123456789','9999999999')
> > '9',null) is null
> >
> > or thereabouts
> >
>
> or more efficiently....
>
> where translate(col, ' 0123456789', ' ') is null
>
>
> Richard
Received on Thu Nov 08 2001 - 06:48:20 CST