Multiple like conditions

I can't seem to find the answer to this question in the SQL docs or the group archives. I've even tried some experimentation without luck. I'm doing a select with multiple conditions AND'ed together. On one field, I'm trying to use wildcards to match one of several conditions. What I seem to need is a combination of "LIKE" and "IN" but that doesn't work. My sample select looks like: SELECT * FROM table WHERE foo=bar AND blah=bleh AND then I want to match one of several possible combinations target LIKE "%AA%%01%" OR target LIKE "%BB%%02%" OR target LIKE "%CC%%03%" I've tried using parentheses to partition the OR's from the other conditions, and actually tried more things than I remember. I feel like I'm blocking on an obvious answer. Any help is appreciated. -- John White

johnjohn-gg@triceratops.com wrote: > I can't seem to find the answer to this question in the SQL docs or the > group archives. I've even tried some experimentation without luck. > > I'm doing a select with multiple conditions AND'ed together. On one > field, I'm trying to use wildcards to match one of several conditions. > What I seem to need is a combination of "LIKE" and "IN" but that > doesn't work. > > My sample select looks like: > > SELECT * FROM table > WHERE foo=bar > AND blah=bleh > AND> > then I want to match one of several possible combinations > target LIKE "%AA%%01%" > OR target LIKE "%BB%%02%" > OR target LIKE "%CC%%03%" > > I've tried using parentheses to partition the OR's from the other > conditions, and actually tried more things than I remember. I feel > like I'm blocking on an obvious answer. Any help is appreciated. What was wrong with: SELECT * FROM table WHERE foo=bar AND blah=bleh AND (target LIKE "%AA%%01%" OR target LIKE "%BB%%02%" OR target LIKE "%CC%%03%") This should do the trick quite happily - so if it doesn't, we're going to need evidence (SET EXPLAIN ON, for instance) and version information. The double-% in the middle of those expressions is suspect -- a string of zero or more instances of any character followed by another such string is equivalent to a single string of zero or more instances of any character. However, that shouldn't break anything; it might slow things down, but that's all. With some slight imprecission - you could also use: target MATCHES "*[ABC][ABC]*0[123]*" This permits xABy03z through which the other doesn't - your call as to whether that matters or not. There isn't any notation directly equivalent to: target LIKE ANY OF { "%AA%01%", "%BB%02%", "%CC%03%" } Well, I suppose you could achieve more or less the result with: CREATE TEMP TABLE LikePatterns (Pattern CHAR(7) NOT NULL); INSERT INTO LikePatterns VALUES("%AA%01%"); INSERT INTO LikePatterns VALUES("%BB%02%"); INSERT INTO LikePatterns VALUES("%CC%03%"); SELECT * FROM table t, LikePatterns p WHERE t.foo = t.bar AND t.blah = t.bleh AND t.target LIKE p.pattern; I'm not sure that I mentioned efficiency, but I think it would work. And you could probably futz with some variant of: TABLE(MULTISET(...)::ROW(pattern CHAR(7))) AS LikePatterns in the FROM clause - I'm not sure the brevity (such as it is) is worth the pain of working out the syntax. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/