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:

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','))

CodePudding user response：

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.