Re: to_number from a varchar field.

From: Stephan Bressler <stephan.bressler_at_siemens.com>
Date: Mon, 10 Feb 2003 12:21:51 +0100
Message-ID: <b281tu$13k$1@news.mch.sbs.de>

"heyvaert" <pol.heyvaert_at_acunia.com> wrote in message news:3E477A83.4030706_at_acunia.com...
> I would like to make a single select statement which returns a number
> (if possible) an 0 or NULL if the field is not filled with a number.
> Which statement can I use in stead of :
>
> SELECT TO_NUMBER(col1) FROM testtable;
>
>

Hi,

here's a small function. The trick is to convert everything to number but catch not-a-number exceptions (instead of others there's a special exception which I currently can't rememeber. but it makes no difference).

Stephan

CREATE OR REPLACE FUNCTION to_number(num VARCHAR2) RETURN NUMBER Is

BEGIN RETURN (num+0);

EXCEPTION WHEN OTHERS THEN RETURN (0); END x_to_number; Received on Mon Feb 10 2003 - 05:21:51 CST