Re: Select numeric data fom varchar but in a range of values

You could try this function :

create or replace function isnumber(txt VARCHAR2) return varchar2 as dummy number;
begin
select to_number(txt) into dummy from dual; return 'Y';
exception
when others then
return 'N';
end;

and add in your query :

... and isnumber(aircraft_reg) = 'Y'...

hth

Yann

Stuart Smith a écrit dans le message
<3c47d4b7.0109270115.1a0a1cf5_at_posting.google.com>...
>I have a varchar cloumn that can contain both numeric and alphanumeric
>data.
>
>The following query works OK to select numeric data only between a
>range of values
>
>select aircraft_reg from flight_leg
>WHERE to_number(aircraft_reg) BETWEEN 9000 AND 9999
>AND instr(translate(upper(aircraft_reg), 'ABCDEFGHIJKLMNOPQRSTUVWXYZ',
>'XXXXXXXXXXXXXXXXXXXXXXXXXXX'), 'X') = 0
>
>but if I transpose the two lines of the WHERE clause thus
>
>select aircraft_reg from flight_leg
>WHERE instr(translate(upper(aircraft_reg),
>'ABCDEFGHIJKLMNOPQRSTUVWXYZ', 'XXXXXXXXXXXXXXXXXXXXXXXXXXX'), 'X') = 0
>AND to_number(aircraft_reg) BETWEEN 9000 AND 9999
>
>then I get ORA-01722 invalid number.
>
>This appears to be because ORACLE evaluates the WHERE clause from the
>last condition to the first condition.
>
>Obviously, I could leave the code as the first select above but I
>would like to write some SQL that is compeletely independent of the
>ORACLE SQL parser/evaluator.
>
>Any suggestions??
>
>Thanks
Received on Thu Sep 27 2001 - 05:02:16 CDT