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?
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
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.
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
| id | keyword |
|----|----------|
| 1 | java,php |
| 3 | java |
| 4 | php |
find_in_set?