How to use SQL LIKE condition with multiple values in PostgreSQL?

41

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.

This question is tagged with sql postgresql postgresql-9.1

~ Asked on 2012-10-18 15:17:39

The Best Answer is


51

Perhaps using SIMILAR TO would work ?

SELECT * from table WHERE column SIMILAR TO '(AAA|BBB|CCC)%';

~ Answered on 2012-10-18 15:20:36


23

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%'));

~ Answered on 2012-10-19 12:14:40


Most Viewed Questions: