I am trying to convert the Comma separated string into an integer array (integer[]) to use in Where clause.
I have tried cast, ::Int which didn't work. Appreciate your input
Example
Table A | Table B
ID | Set_id
2 | 14,16,17
1 | 15,19,20
3 | 21
My Query:
Select *
from Table a, table b
where a.id in b.set_id
You need to convert the string to a proper integer array if you want to use that for a join condition.
Select *
from Table a
join table b on a.id = any(string_to_array(b.set_id, ',')::int[]);
But a much better solution would be to properly normalize your tables (or at least stores those IDs in an integer array, not a varchar column)
Select * from Table_a a, table_b b
where a.id = any(regexp_split_to_array(b.set_id,',')::int[]);
You could use unnest() function. unnest function is to used expand an array to a set of rows.
Select * from Table_a a, table_b b where a.id in (SELECT unnest(string_to_array(b.set_id, ',')::int[]));
