CODEWITHSUNDEEP
arraysstringpostgresql
MeetJoeBlack
MeetJoeBlack

Reputation: 2924

PostgreSQL implement CONTAINS for array of strings

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?

Upvotes: 0

Views: 1340

Answers (1)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125544

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

Upvotes: 1

Related Questions