Re: SQL Question???

Richard, Your solution will work if the "column1" is a character or varchar field but I believe Mark Truty indicated that the "column1" is a numeric field. And I believe the "mod" function is only available on version 6.0 isql and not before. Anyway, the exact syntax for the mod function is mod(dividend,divisor). }Richard wrote: }Hi all, } }What's wrong with: } }select * from tab1 where column1[4,5] = "87" }Simple :-) }I'll admit I've never heard of "mod", but the above }solution works fine for me! }Cheers, }Richard. } From ilist@rmy.emory.edu Thu Oct 19 03:33:43 1995 } From: proberts@lynx.informix.com (Paul Roberts) } Subject: Re: SQL Question??? } Date: 18 Oct 1995 21:30:10 GMT } To: informix-list@rmy.emory.edu } X-Informix-List-To: rt10hp@csl.gov.uk } X-Informix-List-Id: <news.18044> } } In article <463ihb$q72@ixnews7.ix.netcom.com>, } Mark Truty <marktrut@ix.netcom.com> wrote: } > } >I am trying to do a SQL query on a numeric field. } >How do I query the last 2 numbers of a 5 digit field? } >Example: number field = 54387 - I want to search for all records that } >end in 87. } >Thanks } } } I really feel that we ought to be able to say: } } select * } from tab1 } where column1 mod 100 = 87 } } But last time I looked, the "mod" function was available in 4GL but } not in SQL. } } Here's a horribly clunky solution (which I present only in order to } encourage others to present their far more elegant solutions. That's } my story and I'm sticking to it): } } select tab1.*, "xxxxxxxx" dummy_field } from tab1 } into temp t1 with no log ; } } update t1 set dummy_field = column1 ; } } [Or explicitly create a temp table "t1" with a character } column "dummy_field" in place of tab1's integer column } "column1" and then: } } insert into t1 select * from tab1 } } I hate typing create table statements, so I do it as above] } } select * from t1 where dummy_field matches "*87" } } - Paul } Regards, Francis Chue American Tritech 10717 Sorrento Valley Rd. San Diego, CA 92121 email:fsc0@tritech.com