Re: select only numeric value from varchar2 column

Not fast (full-table-scan!), but it works:

create or replace function my_to_number (x varchar2) return number
is
  invalid_number exception;
  pragma exception_init(invalid_number,-6502); begin
  return to_number(x);
exception when invalid_number then
  return null;
end;
/
select * from toto where my_to_number(col1) is not null;

Andy,

Ing. Andreas Vogler
ETM Aktiengesellschaft
Kasernenstr. 29, A-7000 Eisenstadt
Tel. +43-2682-741-0, Fax-107
E-Mail: vogler_at_etm.at
Web: < <http://www.etm.at>>

Bruno Decraene schrieb in Nachricht <37942CB2.B4F2F2BA_at_atos-group.com>...
>I have a table toto with varchar column col1
>
>col1 contains alphanumeric values and numeric values and i want to
>select only the numeric values
>
>How can i do this
>
>Thank you
>
>ansewer to me to rchennaa_at_atos-group.com
>
>Rach
>
>
>
Received on Tue Jul 20 1999 - 03:47:56 CDT