Re: Numeric Function

Good one Rich, prefer your version since it deals with 'e's.

M

Norman Dunbar <Norman.Dunbar_at_lfs.co.uk> wrote in message news:<E2F6A70FE45242488C865C3BC1245DA734F78C_at_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 Fri Nov 09 2001 - 02:49:42 CST