Skip to content
Advertisement

Query condition where a value is in a comma separated string

I have a table in which one of the table columns third_row stores a comma-separated list of numbers as a string but when its value is A then it means a combination of all the possible numbers. How do I approach this so that the query returns all the rows that have the third_row as A and the rest where third_row is equal to one of the values in the comma-separated string?

For reference, here is the format of the table:

first_row second_row third_row
0028001070200 50 A
0049048000701 51 01,04,02,31,

I have also tried this query but no luck:

SELECT
    sds.scheme_code,
    rs.scheme_name
FROM
trea.salary_deduction_schemes sds
LEFT JOIN
trea.receipt_schemes rs
ON sds.scheme_code = rs.scheme_code
WHERE sds.list_object_head = 'A'
OR 16 IN(regexp_split_to_table(sds.list_object_head, E','))

Advertisement

Answer

Your method almost works:

WHERE sds.list_object_head = 'A' OR
      16 IN (SELECT val::int
             FROM regexp_split_to_table(sds.list_object_head, E',') val
            )

You can also use string matching:

WHERE ',' || sds.list_object_head || ',' LIKE '%%,' || 16 || ',%%'

Or you could convert to an array and use array operations.

I would strongly suggest that find a representation other than strings for storing integer values — preferably another table or perhaps an array.

User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement