Re: exception free to_number() subsitute?

From: Michael Serbanescu <mserban_at_postoffice.worldnet.att.net>
Date: 1997/09/12
Message-ID: <3419DA82.4140@postoffice.worldnet.att.net>#1/1

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 Fri Sep 12 1997 - 00:00:00 CDT