Re: select only numeric values from varchar2 column

Bruno Decraene <bdecraene_at_atos-group.com> wrote in message news:37942D3E.FCC3641C_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

There are no build-in functions to accomplish. You may use a user-defined function.

create or replace function IsNumeric(c varchar2) return varchar2 is
  n number;
begin
  n:=to_number(c);
  return 'Y';
exception
  when value_error then
    return 'N';
  when others then
    null;
end;
/

select * from toto where isnumeric(col1)='Y'; Received on Tue Jul 20 1999 - 23:37:56 CDT