Re: Numeric Function

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 - 14:49:42 CST