CODEWITHSUNDEEP
postgresql
scandel
scandel

Reputation: 1822

PostgreSQL: Where clause using LIKE, ANY, and wildcards

I have a table with a field containing an array of strings (type is character varying(255)[]).

I'd like to compare a given string with a wildcard, say 'query%', to any of the elements of this field.

This request works and gets back the expected results: 

SELECT * FROM my_table WHERE 'query' ILIKE ANY(my_field)

But with the wildcard, I got no results: 

SELECT * FROM my_table WHERE 'query%' ILIKE ANY(my_field)

I think the reason is that the wildcard is supported only at the right side of the ILIKE operator, but ANY(my_field) also has to be after the operator.

Is there a way to achieve what I want?

Using PostgreSQL 9.5.

Upvotes: 3

Views: 2455

Answers (2)

Serg M Ten
Serg M Ten

Reputation: 5606

Convert the array into a set with unnest() and use an EXIST clause

SELECT * FROM my_table t WHERE  EXISTS (SELECT unnest(t.my_field) AS f WHERE f ILIKE ‘query%’)

Upvotes: 2

klin
klin

Reputation: 121504

You have to unnest the array field:

with my_table(my_field) as (
values
    (array['query medium', 'large query']),
    (array['large query', 'small query'])
)
select t.* 
from my_table t,
lateral unnest(my_field) elem
where elem ilike 'query%';

            my_field            
--------------------------------
 {"query medium","large query"}
(1 row)

Upvotes: 3

Related Questions