Select numeric data fom varchar but in a range of values

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 - 04:15:04 CDT