How to use SQL LIKE condition with multiple values in PostgreSQL?
SqlPostgresqlPostgresql 9.1Sql Problem Overview
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.
Sql Solutions
Solution 1 - Sql
Perhaps using SIMILAR TO would work ?
SELECT * from table WHERE column SIMILAR TO '(AAA|BBB|CCC)%';
Solution 2 - Sql
Use LIKE ANY(ARRAY['AAA%', 'BBB%', 'CCC%']) as per this cool trick @maniek showed earlier today.
Solution 3 - Sql
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%'));
Solution 4 - Sql
You can use regular expression operator (~), separated by (|) as described in Pattern Matching
select column_a from table where column_a ~* 'aaa|bbb|ccc'
Solution 5 - Sql
Following query helped me. Instead of using LIKE, you can use ~*.
select id, name from hosts where name ~* 'julia|lena|jack';
Solution 6 - Sql
You might be able to use IN, if you don't actually need wildcards.
SELECT * from table WHERE column IN ('AAA', 'BBB', 'CCC')