up vote 3 down vote favorite
1

Is there any shorter way to look for multiple matches:

 SELECT * 
 from table 
 WHERE column LIKE "AAA%" 
    OR column LIKE "BBB%" 
    OR column LIKE "CCC%"

This questions applies to PostgreSQL 9.1, but if there is a generic solution it would be even better.

share|improve this question
It reads well, works well and is easy to understand. Why swap that for alternatives like the popular answer and get into knots when you need to find pipes (|) or brackets in actual data? – RichardTheKiwi Oct 18 '12 at 16:07
@RichardTheKiwi Because is it not as easy to dynamically build. See my answer on how to avoid dealing with a regex. – Clodoaldo Neto Oct 19 '12 at 12:25
@Clo It's still code that's not ISO standard and harder to understand by comparison. Except possibly any (values('AAA%'), ('BBB%'), ('CCC%') That one looks good. – RichardTheKiwi Oct 19 '12 at 12:30

3 Answers

up vote 5 down vote accepted

Perhaps using SIMILAR TO would work ?

SELECT * from table WHERE column SIMILAR TO '(AAA|BBB|CCC)%';
share|improve this answer
+1 SIMILAR TO would work, but for the example in the question it should be SIMILAR TO 'AAA%|(BBB|CCC)%' or SIMILAR TO '(AA%|BBB%|CCC%' – GarethD Oct 18 '12 at 15:39
up vote 3 down vote

Using array or set comparisons:

create table t (str text);
insert into t values ('AAA'), ('BBB'), ('DDD999YYY'), ('DDD099YYY');

select str from t
where str like any ('{"AAA%", "BBB%", "CCC%"}');

select str from t
where str like any (values('AAA%'), ('BBB%'), ('CCC%'));

It is also possible to do an AND which would not be easy with a regex if it were to match any order:

select str from t
where str like all ('{"%999%", "DDD%"}');

select str from t
where str like all (values('%999%'), ('DDD%'));
share|improve this answer
up vote 2 down vote

Use LIKE ANY(ARRAY['AAA%', 'BBB%', 'CCC%']) as per this cool trick @maniek showed earlier today.

share|improve this answer
You just missed the syntax. The list must be array or set. – Clodoaldo Neto Oct 19 '12 at 12:21
@Clodoaldo Whoops, you're quite right. Thankyou. – Craig Ringer Oct 19 '12 at 15:20

Your Answer

 
or
required, but never shown
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.