PostgreSQL: Array of LIKEs

Now you may ask what is wrong with that? Well on its own yes, nothing…. but what about when we need to search for 5 product conditions at once? The query will get just kind of ugly and unwieldy!

1
2
3
4
5
6
7
8
9
10
11
SELECT sum(product_cost) FROM t_test
WHERE product_code LIKE '%123%'
OR product_code LIKE '%234%'
OR product_code LIKE '%345%'
OR product_code LIKE '%456%'
OR product_code LIKE '%567%';

Not something I normally enjoy writing…so can we do better? Yes, we can! Say hello to our “array of LIKEs”:

1
2
3
4
5
6
7
SELECT sum(product_cost) FROM t_test WHERE product_code LIKE ANY( array['%123%', '%234%', '%345%', '%456%', '%567%']);
-- or the same using shorter Postgres array notation
SELECT sum(product_cost) FROM t_test WHERE product_code LIKE ANY(‘{%123%,%234%,%345%,%456%,%567%}’);