CODEWITHSUNDEEP
sqlstringpostgresqlcsvinner-join
Landon Statis
Landon Statis

Reputation: 839

Postgres Query based on Delimited String

we have a column in one of our tables that consist of a delimited string of ID's, like this: 72,73,313,502.

What I need to do in the query, is to parse that string, and for each ID, join it to another table. So in the case of this string, I'd get 4 rows.......like the ID and the name from the other table.

Can this be done in a query?

Upvotes: 0

Views: 58

Answers (1)

GMB
GMB

Reputation: 222462

One option is regexp_split_to_table() and a lateral join. Assuming that the CSV string is stored in table1(col) and that you want to join each element against table2(id), you would do:

select ...  -- whatever columns you want
from table1 t1
cross join lateral regexp_split_to_table(t1.col, ',') x(id)
inner join table2 t2 on t2.id = x.id::int

It should be noted that storing CSV strings in a relational database is bad design, and should almost always be avoided. Each value in the list should be stored as a separate row, using the proper datatype. Recommended reading: Is storing a delimited list in a database column really that bad?

Upvotes: 1

Related Questions