Re: exception free to_number() subsitute?

From: Michael Serbanescu <mserban_at_postoffice.worldnet.att.net>
Date: 1997/09/16
Message-ID: <5vnk9b$780@bgtnsc02.worldnet.att.net>#1/1

Are you sure ? It works for me when I run: SELECT chr2nbr(<col_name>) FROM <table_name>. Is this a pure SQL query? Let me know.

Michael Serbanescu

---

Dan Clamage wrote:
>
> 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 Tue Sep 16 1997 - 00:00:00 CDT