RE: having a senior moment and can't fingure

Your criteria would include '00ZZ' which is <= '1111'. I doubt that this is what Kathy wants. Here is a bulletproof method:

create or replace function atoi(str in varchar) return integer deterministic as
NaN exception;
pragme exception_init(NaN,-1722);
begin

   return(to_number(str));
exception

   when NaN then return(NULL);
end;
/         

Then your query becomes

select * from t1 where atoi(substr(col,1,4)) is not null;

Of course, the point that needs to be made is that your database is incorrectly designed. If the first 4 character have special meaning, then they deserve to be an attribute (a column,in plain Oracle English). Kathy, don't worry about the senior moment. With the new stem cell research, they'll discover the cure for aging in no time. Ponce de Leon's dream will become something marketed by the big drug companies, just like the little blue pill that took place of the love potion number 9.

--
Mladen Gogala
A & E TV Network
Ext. 1216



> SQL> select d from t1 where length(d) = 4 and d between '0000' and 

> SQL> '9999';

--
http://www.freelists.org/webpage/oracle-l