Re: How do I test if a value is numeric?

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: 18 Dec 2002 06:13:10 -0800
Message-ID: <2687bb95.0212180613.7d4da8a6@posting.google.com>

"Graeme Richardson" <graeme_at_adept_zero_spam_x.co.nz> wrote in message news:<atomdi$8dk$1_at_news.wave.co.nz>...
> I have a Char(30) field in a database that has been used to store a
> variable length persons name and optionally a 2 decimal number (e.g. 12.34,
> and 2.34). The number is always preceded by at least one space.
>
> I need records where there is a number on the end. How do I write a clause
> to do this?
>
> I have clause that converts the last six characters to a numeric value (used
> in Select clause):
>
> CAST(SUBSTRING(CB_TR_NARRATIVE FROM POSITION (' ' IN CB_TR_NARRATIVE FROM
> 24) AS NUMERIX(4,2)) AS Actual
>
> but for the case where a number is not included, this fails.
>
> Thanks for any help you can offer,

1 declare
  2 v_variable varchar2(38);
  3 function is_number(
  4 p_input in varchar2
  5 ) return boolean is
  6 v_conversion number := 0;
  7 begin
  8 if p_input is null then
  9 return FALSE;
 10 end if;
 11 v_conversion := to_number(p_input);  12 return TRUE;
 13 exception
 14 when others then
 15 return FALSE;
 16 end is_number;
 17 --
 18 begin
 19 v_variable := '12345';
 20 if is_number(v_variable)

 21     then dbms_output.put_line(v_variable||' is a number');
22     else dbms_output.put_line(v_variable||' is not a number');

 23 end if;
 24 v_variable := '12a45';
 25 if is_number(v_variable)

 26     then dbms_output.put_line(v_variable||' is a number');
 27     else dbms_output.put_line(v_variable||' is not a number');

 28 end if;
 29* end;
UT1> /
12345 is a number
12a45 is not a number

PL/SQL procedure successfully completed.

There are many improvements that need to be made to this routine based on your needs. Examples would include setting a pragma for the actual "invalid number" error 01722 so that you would call raise_application_error on any other error. You might want to return a numeric null for invalid and the value for valid instead of TRUE/FALSE. The code as written will recognize a decimal point but you may need a routine that can handle commas and perhaps '$' in the input and strip them out then convert.

HTH -- Mark D Powell -- Received on Wed Dec 18 2002 - 08:13:10 CST