Example 1
Split a comma-separated tags string into one row per tag
The string 'sql,database,query' contains three comma-separated values. After splitting, each becomes its own row. The result is sorted alphabetically — database, query, sql. SQL Server's STRING_SPLIT yields a table with a column named value. PostgreSQL's REGEXP_SPLIT_TO_TABLE yields an unnamed column, aliased here as item using the AS s(item) syntax. The MySQL/MariaDB JSON_TABLE workaround wraps the string in [", replaces , with ",", closes with "] to form valid JSON, then iterates over $[*] to produce one row per element.
CREATE TABLE posts (id INT, tags VARCHAR(200));
INSERT INTO
posts (id, tags)
VALUES
(1, 'sql,database,query');SELECT
TRIM(s.value) AS tag
FROM
posts p CROSS APPLY STRING_SPLIT (p.tags, ',') s
WHERE
p.id = 1
ORDER BY
tag;| tag |
|---|
| database |
| query |
| sql |
CREATE TABLE posts (id INT, tags VARCHAR(200));
INSERT INTO
posts (id, tags)
VALUES
(1, 'sql,database,query');SELECT
TRIM(s.item) AS tag
FROM
posts p
CROSS JOIN REGEXP_SPLIT_TO_TABLE (p.tags, ',') AS s (item)
WHERE
p.id = 1
ORDER BY
tag;| tag |
|---|
| database |
| query |
| sql |
SQL Server uses STRING_SPLIT via CROSS APPLY. PostgreSQL uses REGEXP_SPLIT_TO_TABLE via CROSS JOIN. MySQL and MariaDB are unsupported natively; the shown workaround converts the string into a JSON array and expands it with JSON_TABLE. All four queries produce the same three rows sorted alphabetically.