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.
Perhaps using SIMILAR TO would work ?
SELECT * from table WHERE column SIMILAR TO '(AAA|BBB|CCC)%';
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%'
Similar to was added to Postgres 7.3, so Netezza systems and the like may not allow its use.
Use LIKE ANY(ARRAY['AAA%', 'BBB%', 'CCC%']) as per this cool trick @maniek showed earlier today.
ANY was added in Postgres 7.4.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%'));
ANY and ALL were added in Postgres 7.4.You can use regular expression operator (~), separated by (|) as described in Pattern Matching
select column_a from table where column_a ~* 'aaa|bbb|ccc'
Following query helped me. Instead of using LIKE, you can use ~*.
select id, name from hosts where name ~* 'julia|lena|jack';
if you want use not like to skip values use this query example:
select * from table
where not field like any(array['%aaa%','%bbb%','%ccc%','%ddd%'])
You might be able to use IN, if you don't actually need wildcards.
SELECT * from table WHERE column IN ('AAA', 'BBB', 'CCC')
|) or brackets in actual data?any (values('AAA%'), ('BBB%'), ('CCC%')That one looks good.