Re: exception free to_number() subsitute?

Allard Siemelink wrote:
>
> Within a select which returns multiple rows, I need to do a conversion from a
> varchar2 field to a number. However, it is essential that the select will not
> break when the varchar2 happens to *not* contain a valid number.
> Basically, what I need is a to_number() substitute that will return a NULL when
> the string cannot be converted. Also, it needs to be *fast*.
>
> Any suggestions? (short of writing a dedicated function that will parse the
> varchar2 field character by character)
>
> TIA,
>
> -Allard

Please excuse the brevity - I'm a lazy typist...

create function my_to_number(x varchar2) return number is   y number;
begin
  y := to_number(x); return y
  exception when value_error then return null; end;

-- 
---------------------------------------------
Connor McDonald
BHP Information Technology
Perth
Australia
email: mcdonald.connor.cs_at_bhp.com.au
"My views are not BHP ...blah blah"
---------------------------------------------
"Opera singers are the only people that when you 
 stick a knife in them, they sing, not bleed"