RE: having a senior moment and can't fingure
- From: "Gogala, Mladen" <Mladen.Gogala@xxxxxxxx>
- To: "'davidsharples@xxxxxxxxx'" <davidsharples@xxxxxxxxx>, "Oracle L (E-mail)" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 26 Oct 2004 12:23:19 -0400
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';
--
//www.freelists.org/webpage/oracle-l
Other related posts: