Re: Avoiding ORA-01722 Invalid Number

"Jeremy" <newspostings_at_hazelweb.co.uk> wrote in message news:MPG.1d0f4bfd4a5ab270989ea8_at_news.individual.net...
> Platform: Oracle 9iR2
>
>
>
> For the following, please don't tell me to 'change my design' - I just
> want to know whether there is a way of coding the select such that it
> won't return an error.....
>
>

create or replace function IsNumber(s varchar2, format varchar2) is   a number;
begin
  if format is not null then
    a:=to_number(s, format);
  else
    a:=to_number(s);
  end if;
  return 1;
exception when others then
  return 0;
end;

select * from T1 where decode(IsNumber(charcol),1,to_number(charcol),0) between 20 and 30

There will be some performance issues, but this way is most reliable to skip ANY non-number strings. Received on Tue Jun 07 2005 - 03:32:23 CDT