PostgreSQL implement CONTAINS for array of strings

Question

I need to check whether a string contains(LIKE) one of the substrings from separated by space(" ") delimiter values.

For example, I need to select rows where c.name field (with 'TEST-DOWNEY' value for ex.) contains one of the substrings from 'TEST CLIN HOSP' string.

I try to use LIKE ANY operator, but it requires '%' char before and after each substring in the array:

SELECT c.name FROM contact c
WHERE c.name LIKE ANY (regexp_split_to_array('TEST CLIN HOSP', E' '))

But if I run it like this:

SELECT c.name FROM contact c
    WHERE c.name LIKE ANY (regexp_split_to_array('%TEST% %CLIN% %HOSP%', E' '))

it works.

So how can I dynamically add '%' chars to every substring in the array after splitting it to an array?

Clodoaldo Neto · Accepted Answer · 2017-05-08 15:51:55Z

1

with contact (name) as (values ('TEST-DOWNEY'))
select c.name
from contact c
where (
    select sum(strpos(c.name, p))
    from regexp_split_to_table('TEST CLIN HOSP', E' ') p(p)
) > 0
;
    name     
-------------
 TEST-DOWNEY

answered May 8, 2017 at 15:51

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL implement CONTAINS for array of strings

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related