How To Use Sql Like Condition With Multiple Values In Postgresql?

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 appli

Solution 1:

Perhaps using SIMILAR TO would work ?

SELECT*fromtableWHEREcolumnSIMILARTO'(AAA|BBB|CCC)%';

Solution 2:

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

Solution 3:

Using array or set comparisons:

createtable t (str text);
insertinto t values ('AAA'), ('BBB'), ('DDD999YYY'), ('DDD099YYY');

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

select str from t
where str likeany (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 likeall ('{"%999%", "DDD%"}');

select str from t
where str likeall (values('%999%'), ('DDD%'));

Solution 4:

You can use regular expression operator (~), separated by (|) as described in Pattern Matching

select column_a fromtablewhere column_a ~*'aaa|bbb|ccc'

Solution 5:

Following query helped me. Instead of using LIKE, you can use ~*.

select id, name from hosts where name ~* 'julia|lena|jack';