Re: Simple problem...

> You can do it without exceptions if you use translate to remove all the
> numeric characters and check for null. See this example below. Note the
> 'x' is just a dummy translation to itself so that transalate won't always
> return null.
>
> SQL> select * from t;
>
> STRING
> --------------------
> 12345
> a12345
> 12b345
> 12b345xx
> 987
> 987.01
> 1,987.01
> 987*
> 999
> 953
>
> 10 rows selected.
>
> SQL> select to_number(string)
> 2 from t where
> 3 translate(string,'x0123456789','x') is null;
>
> TO_NUMBER(STRING)
> -----------------
> 12345
> 987
> 999
> 953

Unfortunately, this solution cannot handle strings in the scientific notation (how it is called, I believe) which is considered a valid number in oracle:

SQL> select '5e8' * '3e-2' from dual;

'5E8'*'3E-2'

    15000000

Not to mention that negative numbers cannot be converted, either.  

> SQL> select to_number(string)
> 2 from t where
> 3 translate(string,'x.0123456789','x') is null;
>
> TO_NUMBER(STRING)
> -----------------
> 12345
> 987
> 987.01
> 999
> 953

This is a problem if a string has more than one dots in it.

Rene Nyffenegger

-- 
  no sig today