Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: having a senior moment and can't fingure

RE: having a senior moment and can't fingure

From: Gogala, Mladen <Mladen.Gogala_at_aetn.com>
Date: Tue, 26 Oct 2004 12:23:19 -0400
Message-ID: <30462D80AA52E74698512ADCC4F7EAA317C1FC83@EXCHANGE>


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
Received on Tue Oct 26 2004 - 11:19:34 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US