SQL / TEXT, NUMERIC, AND DATE FUNCTIONS
Splitting and joining delimited values
Split a comma-separated column into one row per value, then rebuild delimited strings with string_agg in a deterministic order.
What you will learn
- Explode one delimited cell into rows with unnest(string_to_array(col, ',')) in FROM
- Keep element positions with WITH ORDINALITY instead of guessing order later
- Rebuild a list with string_agg(tag, ',' ORDER BY tag) so output order is deterministic
- Read a single field with split_part, checking for '' rather than NULL when absent
Understanding Splitting and joining delimited values
A delimited column such as 'red,green,blue' is a list folded into a single cell, and everything that makes SQL useful (joins, GROUP BY, indexes, foreign keys) operates on rows, not on substrings. That is why the two directions of this problem need two different kinds of function: splitting uses a set-returning function that sits in the FROM clause and multiplies one input row into as many rows as there are elements, while joining uses an aggregate that collapses a group of rows into one string. Neither is a scalar transformation, which is exactly why the row count changes in both directions. The one scalar in this family is split_part, which picks a single field and hands back one value, leaving the row count alone.
In PostgreSQL, string_to_array(tags, ',') builds a text[] and unnest turns that array into rows. A function call in FROM may reference columns from tables listed before it (an implicit LATERAL), so you can write it right next to the table it feeds from. WITH ORDINALITY adds a bigint column holding each element's 1-based position, and that is the only honest way to recover order after a split, since rows have no inherent order; regexp_split_to_table(tags, '\s*,\s*') covers the case where the delimiter itself is padded. Other engines spell it differently: SQL Server's STRING_SPLIT returns a table with a value column, MySQL has no split function at all so SUBSTRING_INDEX or JSON_TABLE stands in, and SQLite needs a recursive CTE.
Going the other way, string_agg(tag, ',' ORDER BY tag) is the PostgreSQL and SQL Server form, with SQL Server placing the ordering in WITHIN GROUP; MySQL and SQLite use GROUP_CONCAT and Oracle uses LISTAGG. The ORDER BY inside the parentheses matters more than it looks, because without it the concatenation order is whatever the executor produced, so the same rows can yield different strings after an index change or a plan change. Two more behaviours are worth memorising: the delimiter appears only between elements, so a one-row group gets no comma, and NULL inputs are skipped entirely, so a group of only NULLs returns NULL rather than an empty string. Treat both operations as edge work, parsing on import and assembling for output, since a delimited column cannot be indexed per element and LIKE '%red%' cheerfully matches 'darkred'.
WITH orders(id, tags) AS (
VALUES (1, 'red,green,blue'),
(2, 'blue'),
(3, 'red, blue')
)
SELECT o.id,
t.pos,
btrim(t.tag) AS tag
FROM orders AS o,
unnest(string_to_array(o.tags, ',')) WITH ORDINALITY AS t(tag, pos)
ORDER BY o.id, t.pos;Splitting and joining are cardinality changes rather than text tricks: a set-returning function turns one delimited cell into many rows, and an aggregate turns many rows back into one string.
Worked examples
Joining rows back into one string
Collapses many tag rows into one delimited value per order, with and without duplicates.
WITH order_tags(order_id, tag) AS (
VALUES (1, 'red'),
(1, 'green'),
(1, 'blue'),
(2, 'blue'),
(2, 'blue')
)
SELECT order_id,
string_agg(tag, ',' ORDER BY tag) AS all_tags,
string_agg(DISTINCT tag, ',' ORDER BY tag) AS unique_tags
FROM order_tags
GROUP BY order_id
ORDER BY order_id;Example explained
Line 1string_agg is an aggregate, so the five input rows reduce to one row per GROUP BY key.
Line 2ORDER BY tag inside the parentheses belongs to the aggregate call, not to the query, and it fixes the order of the concatenated elements.
Line 3Order 2's two identical rows give 'blue,blue'; DISTINCT removes the duplicate before joining, and with DISTINCT the ORDER BY key must be the aggregated expression itself.
Line 4The comma sits only between elements, so nothing trails 'red' and the single-element result has no delimiter at all.
Reading one field without expanding rows
Shows split_part picking a positional field, and what it returns when that field is missing.
WITH files(path) AS (
VALUES ('2026/09/invoice.pdf'),
('2026/report.csv')
)
SELECT path,
split_part(path, '/', 3) AS third,
split_part(path, '/', -1) AS last_part,
array_length(string_to_array(path, '/'), 1) AS parts
FROM files
ORDER BY path;Example explained
Line 1split_part is scalar, so two input rows stay two output rows and no FROM-clause function is involved.
Line 2'2026/report.csv' has no third field, so split_part returns an empty string rather than NULL, which is why that cell is blank instead of showing NULL.
Line 3A negative index counts from the right (PostgreSQL 14 and later), the reliable way to grab the last field when the field count varies.
Line 4array_length(string_to_array(path, '/'), 1) counts the fields, useful in a WHERE or CHECK that rejects values with the wrong shape.
Split, clean, rejoin
Normalises a messy delimited value by exploding it, filtering the junk, and aggregating it back.
WITH orders(id, tags) AS (
VALUES (1, 'red,green,,blue'),
(2, 'green')
),
exploded AS (
SELECT o.id, btrim(t.tag) AS tag
FROM orders AS o,
unnest(string_to_array(o.tags, ',')) AS t(tag)
)
SELECT id,
string_agg(tag, ';' ORDER BY tag) AS cleaned
FROM exploded
WHERE tag <> ''
GROUP BY id
ORDER BY id;Example explained
Line 1The doubled comma in 'red,green,,blue' produces a fourth element that is an empty string, not NULL, so WHERE tag <> '' is what removes it.
Line 2btrim runs once per element after the split, which is the only place padding around a delimiter can be stripped reliably.
Line 3string_agg with ORDER BY tag rebuilds the list in a fixed order and with a new delimiter, so the round trip normalises the value instead of copying it.
Line 4Row counts run 2, then 5 after the split, then 4 after the filter, then 2 after the aggregate.
Important notes
unnest of an empty or NULL array yields zero rows, so a row whose list is empty vanishes from a comma-style join; write LEFT JOIN LATERAL unnest(...) AS t(tag) ON true to keep it with a NULL tag.
MySQL's GROUP_CONCAT truncates at group_concat_max_len (1024 bytes by default) and only raises a warning, so long lists come back silently cut off.
Common mistakes
Splitting on ', ' because the sample data happened to have spaces: string_to_array('red,green', ', ') returns a single element containing the whole string, so every downstream join or IN test silently matches nothing.
Calling string_agg or GROUP_CONCAT with no ORDER BY inside the parentheses and then comparing the result to a stored string; element order follows the plan, so the check passes on small data and starts failing once the scan order changes.
Expecting split_part to return NULL for a field that is not there: index 3 of 'a,b' is '', so COALESCE(split_part(...), 'unknown') never fires and the report shows an empty cell instead.
Try it yourself
Change, predict, then run
Build a two-row CTE of recipient lists such as 'a@x.com; b@y.com ;;c@z.com', split on ';', trim each address and drop the empty elements, then rebuild each row as a comma-separated list sorted alphabetically. Add a third row whose list is '' and watch it disappear from the result until you rewrite the split as LEFT JOIN LATERAL ... ON true.
Open the SQL workspaceCheck your understanding
An orders table holds three rows with tags = 'red,green', tags = 'blue', and tags = NULL. You run SELECT o.id, t.tag FROM orders AS o, unnest(string_to_array(o.tags, ',')) AS t(tag). What comes back?
- 3 rows; the order whose tags is NULL produces no rows and drops out of the result
- 4 rows; the NULL order appears once with tag set to NULL
- 4 rows; the NULL order appears once with tag set to an empty string
- An error, because unnest rejects a NULL array argument
Show answer
string_to_array(NULL, ',') is NULL, and unnesting NULL produces zero rows rather than one NULL row; because a function in FROM behaves like an inner lateral join, the parent row has nothing to pair with and is eliminated, leaving 2 + 1 + 0 = 3 rows. Option 2 is tempting because a scalar function such as split_part would return NULL and preserve the row, but a set-returning function changes cardinality instead of producing a value, so keeping that row requires LEFT JOIN LATERAL ... ON true.