CODEWITHSUNDEEP
sqlpostgresqlpostgresql-9.2
Mike
Mike

Reputation: 1922

How to use LIKE with ANY in Postgresql?

Is there a way to do a partial string match on a string array column in postgres?

I'm trying the following syntax, but it is not working as I'd expect it to:

SELECT * FROM example_table WHERE '%partial string' ILIKE ANY(array_column)

Is there a correct way of doing this?

Upvotes: 3

Views: 1067

Answers (3)

tonigiri
tonigiri

Reputation: 177

Just implemented this using array_to_string:

SELECT * FROM example_table WHERE array_to_string(array_column) ILIKE '%partial string'

Upvotes: 1

Emery Lapinski
Emery Lapinski

Reputation: 1662

drop table if exists temp_a;
create temp table temp_a as
(
    select array['alpha','beta'] as strings
    union all
    select array['gamma','theta']
);

select * 
from (select unnest(strings) as string from temp_a) as sq 
where string ilike '%eta'

Upvotes: 1

Joe Love
Joe Love

Reputation: 5932

You need to search each subscript separately -- here's an example that can be expounded on to include more columns

SELECT distinct array_column FROM
   (SELECT array_column,
           generate_subscripts(array_column, 1) AS s
      FROM example_table) AS foo
 WHERE array_column[s] like '%partial string';

Alternative hack:

select * from example_table where array_column::text like '%partial%'

if necessary you could hack "partial" to include the opening/closing brackets and quotes to be just a bit more precise.

Upvotes: 1

Related Questions