Check comma separated string in comma separated column in postgresql

Question

I have a string searchval = "php,java,html".

I want to check any of the comma separated value in string exist in column keyword in my below table.

My table

id     keyword
-----------------------------
1      java,php
2      jquery, javascript
3      java
4      php,jquery

Any of the searchval(comma separated) value match the keyword(comma separated) should return the id.

So the result should be:

1 (java,php is there),
3 (java is there),
4 (php is there)

How can I do this?

Answer 1

You can easily convert a comma separated list into an array, then use Postgres' array functions to test for containment:

select id
from the_table
where string_to_array(keyword,',') && array['php','java','html'];

The overlaps operator && checks if the array created from the keyword list contains elements from the array on the right hand side.

Online example: http://rextester.com/VNWP17762

Answer 2

Firstly, an important advice: do not store comma separated strings in a column in database. It makes processing unnecessarily harder. You should rather consider creating a separate keyword table and have a foreign key on your table on id column.

You could convert the strings to ARRAY and do an overlaps operation.But, to check value by value, you would need to UNNEST it for comparison.

SQL Fiddle

PostgreSQL 9.6 Schema Setup:

CREATE TABLE t
    (id int, keyword varchar(18))
;

INSERT INTO t
    (id, keyword)
VALUES
    (1, 'java,php'),
    (2, 'jquery, javascript'),
    (3, 'java'),
    (4, 'php,jquery')
;

Query 1:

SELECT id, 
       string_agg(lang, ',') AS keyword 
FROM   t, 
       unnest(string_to_array(keyword, ',')) AS k(lang) 
WHERE  lang = ANY ( string_to_array('php,java,html', ',') ) 
GROUP  BY id 
ORDER  BY id

Results:

| id |  keyword |
|----|----------|
|  1 | java,php |
|  3 |     java |
|  4 |      php |