Re: Numeric Function

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 - 12:59:18 CST