CODEWITHSUNDEEP
sqlpostgresqlpattern-matchingsql-like
Евгения Рубанова
Евгения Рубанова

Reputation: 25

SQL Using LIKE and ANY at the same time

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.

Upvotes: 1

Views: 258

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656321

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:

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:

Upvotes: 3

Related Questions