SQL Using LIKE and ANY at the same time

Question

I have a table with a column feature of type text and a text array (text[]) named args. I need to select from the table those rows in which the feature column contains at least one of the elements of the args array.

I've tried different options, including this:

SELECT * FROM myTable WHERE feature LIKE '%' + ANY (args) + '%';

But that does not work.

Answer 1

The simple solution is to use the regular expression match operator ~ instead, which works with strings in arg as is (without concatenating wildcards):

SELECT *
FROM   tbl
WHERE  feature ~ ANY(args);

string ~ 'pattern' is mostly equivalent to string LIKE '%pattern%', but not exactly, as LIKE uses different (and fewer) special characters than ~. See:

• Escape function for regular expression or LIKE patterns

If that subtle difference is not acceptable, here is an exact implementation of what you are asking for:

SELECT *
FROM   tbl t
WHERE  t.feature LIKE ANY (SELECT '%' || a || '%' FROM unnest(t.args) a);

Unnest the array, pad each element with wildcards, and use LIKE ANY with the resulting set.

See:

• IN vs ANY operator in PostgreSQL