to_number from a varchar field.

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;

e.g.

testtable

col1 (varchar2(10)) returned value

-------------------   --------------
12                                12
abcdef                             0 (or NULL)
3298                            3298
justastring                        0 (or NULL)

Thanks

Pol Received on Mon Feb 10 2003 - 04:10:11 CST