Split a delimited column into rows
Turn a comma-separated tags column into one row per tag with DuckDB's unnest(string_split(...)) — the SQL equivalent of exploding a list.
Last updated
SELECT id, unnest(string_split(tags, ',')) AS tag FROM products;
Why it’s written this way
string_split(tags, ',') breaks the delimited text into a list value first — DuckDB has a genuine list type, so at that point the row still holds a single list column. unnest() is what turns that list back into multiple rows, one per element, repeating the row's other columns (id) alongside each one.
The two functions compose in a single SELECT because DuckDB allows unnest() to appear directly in the SELECT list next to ordinary columns, with no LATERAL join or separate CROSS JOIN UNNEST(...) clause required.
Edge cases to know
- →This exact syntax is DuckDB-specific. Postgres needs unnest(string_to_array(tags, ',')); Snowflake and BigQuery reach for SPLIT combined with a lateral FLATTEN or UNNEST — check the target engine before copying this verbatim.
- →An empty tags value produces one empty-string element rather than zero rows — filter with WHERE tag <> '' afterward if a blank shouldn't count as a tag.
- →Whitespace around the delimiter ('a, b, c') ends up inside each element ('a', ' b', ' c'); wrap with trim(unnest(...)) if the source data isn't already clean.
Part by part
- string_split(tags, ',')
- Splits the delimited text into a list. It produces one array value per row rather than several rows, which is why the second function is needed.
- unnest(
- Expands an array into one row per element. Together with the split this turns a single comma-separated column into a proper row-per-tag result.
Common mistakes
string_split(tags, ', ')Splitting on comma-and-space fails whenever the data has no space after the comma, returning the whole string as one element. Split on the comma alone and trim each result instead.
WHERE tags LIKE '%urgent%'Matches any tag containing the substring, so 'urgent' also matches 'not-urgent'. Exploding into rows and comparing exactly is the only reliable way to query a delimited column.
Storing tags as a delimited string in the first placeIt cannot be indexed, cannot be constrained, and every query has to parse it. A join table, or a native array or JSON column, does all three properly — this recipe is for data you inherited.
Questions
What is the equivalent in other databases?
Postgres uses unnest(string_to_array(tags, ',')), SQL Server has STRING_SPLIT as a table function, and MySQL 8 needs a recursive CTE or JSON_TABLE. The concept is universal and no two spellings agree.
How do I remove whitespace around each value?
Wrap the unnested column in TRIM. Splitting on the delimiter alone and trimming afterwards handles both spaced and unspaced input, which splitting on a two-character delimiter cannot.
What happens to an empty string or a NULL?
An empty tags column typically produces one empty-string row, and NULL produces no rows at all. Both are easy to miss, so filter explicitly rather than assuming the counts line up.
Should I fix the schema instead?
If you own it, yes — a tags table with a foreign key gives you indexes, referential integrity and straightforward queries. This pattern is the right tool for a CSV import or a column you cannot change.