Re: Avoiding ORA-01722 Invalid Number

In article <d83m0o$eh1$1_at_news.caravan.ru>, Dmitry E. Loginov says...
>
> "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.
>
>
>

Thanks - nice.

-- 

jeremy