Re: exception free to_number() subsitute?

This doesn't work in a pure SQL query (which is what Allard is running) because the exception handler is considered a side effect and the SQL engine won't accept the function.

Michael Serbanescu <mserban_at_postoffice.worldnet.att.net> wrote in article <3419DA82.4140_at_postoffice.worldnet.att.net>...
> You could try the function below:
>
> CREATE OR REPLACE FUNCTION chr2nbr (colchr in VARCHAR2)
> RETURN NUMBER IS
> colnbr NUMBER;
> BEGIN
> SELECT TO_NUMBER(colchr) INTO colnbr
> FROM dual;
> RETURN colnbr;
> EXCEPTION
> WHEN invalid_number THEN RETURN NULL;
> END;
> /
>
> INVALID_NUMBER is a predefined PL/SQL exception that handles the
> ORA-01722 error that you will get if you try to convert to number an
> inappropriate character string.
>
> Hope this helps.
>
>
>
>
> Michael Serbanescu
> ------------------------------------------------------------------
> 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
>
  Received on Wed Sep 17 1997 - 00:00:00 CDT