particular column has multiple data separated with comma, how to write a query to return that particular row if it has at least one data

Question

For example

col1	col2
1	2,3,4

Referring to the table shown here, if I want to return a row if col1=1 and col2 has 2,3.

How to write query for this scenario?

Bit of a silly question but I am new to SQL and need your help!

Answer 1

You can just use LIKE to detect the comma in column2

SELECT * FROM <table> WHERE COLUMN2 LIKE '%,%'

Answer 2

You can use the string_to_array function to convert your string to an array, then use the ANY in your condition to get the desired strings.

select * from  my_table where 3 = ANY (string_to_array(col2,',')::int[])

Demo in DBfiddle

Answer 3

and col2 has 2,3.

If you are looking for multiple values, you can use the contains operator for arrays (after converting the dreaded CSV value to an array).

select * 
from the_table 
where (string_to_array(col2,','))::int[] @> array[2,3]

This will only return rows where both values (2, 3) are contained in the CSV column.

Note that this won't work if you have spaces before or after the commas. In that case you would need to use regexp_split_to_array()