Re: Numeric Function

Then how about

  select *
    from my_table
  where field is between 0 and <max_num_goes_here>;

So, if the field is number( 9 ) <max_num_goes_here> would be 999999999.

<NeedaHoliday> wrote in message
news:8agout4sb5aelsfjgsvua7sf7s215b8cb2_at_4ax.com...
> No,
>
> I want to select only the numeric values from the table.
>
> On Fri, 09 Nov 2001 20:01:45 GMT, "Scott Mattes"
> <ScottMattes_at_yahoo.com> wrote:
>
> >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 - 16:38:44 CST