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

Home -> Community -> Usenet -> c.d.o.server -> Re: Numeric Function

Re: Numeric Function

From: Scott Mattes <ScottMattes_at_yahoo.com>
Date: Fri, 09 Nov 2001 20:01:45 GMT
Message-ID: <JCWG7.441$Ez3.339503@news1.news.adelphia.net>


If I read you correctly, what you want is some function that takes 'abc123' and spits out '123'. If so, take the previous suggestions and translate the 'ABC's to null instead (besure to change the case of the value to upper case before doing the translate).

<NeedaHoliday> wrote in message
news:2o9outctik3ja40s3o2qtus4pnglr409mu_at_4ax.com...
> What the requirement is, the string value has a mix of charaters and
> number. THe person wants to find the maximum number in the table.
>
> i.e.
>
> abc123
> adg456
> 1234
> 1e234r
> 13456
>
> The person want to find 13456 as the maximum number.
>
>
>
> On Thu, 08 Nov 2001 18:38:35 GMT, Richard Kuhler <noone_at_nowhere.com>
> wrote:
>
> >I interpreted the word 'numeric' to mean composed of all numeric
> >characters rather than any valid number.
> >
> >What about 7.3.2? Is that numeric? Does that pass your function?
> >What about '7 3 2'? Is that numeric? Does that pass your function?
> >What about '7.3E+00'? Is that numeric? Does that pass your function?
> >What about '-1'? Is that numeric? Does that pass your function?
> >(many more special cases) ...
> >
> >If you want a function that tests whether a string is a valid number why
> >not just...
> >
> >function is_numeric (in_value in varchar2)
> >return boolean is
> >begin
> > if to_number(in_value) is null then
> > return null;
> > else
> > return true;
> > end if;
> >
> > exception
> > when others then
> > return false;
> >end;
> >
> >Richard
> >
> >MarkyG wrote:
> >>
> >> 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 - 14:01:45 CST

Original text of this message

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