CODEWITHSUNDEEP
sqlpostgresql
on3ss
on3ss

Reputation: 53

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

Upvotes: 0

Views: 1500

Answers (2)

user330315
user330315

Reputation:

You can convert the list to an array and use the = any operator:

WHERE sds.list_object_head = 'A' 
   OR 16 = any(string_to_array(trim(',' from sds.list_object_head), ',')::int[])

The trim() is necessary to get rid of the trailing , that would result in an empty string after applying string_to_array() and that in turn would result in a casting error as an empty string isn't a valid integer.

This is probably a bit faster than using a regex and unnesting the array.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269823

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.

Upvotes: 2

Related Questions