SQL - PostgreSQL - Split a Single Column into Separate Rows

Source: sql-snippets/split-column-to-rows.md at main · count/sql-snippets (github.com)

View an interactive version of this snippet here.

Description

You will often see multiple values, separated by a single character, appear in a single column.

If you want to split them out but instead of having separate columns, generate rows for each value, you can use the function REGEXP_SPLIT_TO_TABLE:

WITH data AS (
  SELECT *
  FROM (VALUES ('yellow;green;blue'), ('orange;red;grey;black')) AS data (str)
)

SELECT
  REGEXP_SPLIT_TO_TABLE(str,';') str_split
FROM data;

The separator can be any single character (i.e. ',' or /) or something more complex like a string (to&char123), as the function uses Regular Expressions.

Output:

str_split
yellow
green
blue
orange
red
grey
black

---

Appendix: Links

• Code
• SQL
• Databases
• PostgreSQL
• Development

Backlinks:

list from [[SQL - PostgreSQL - Split a Single Column into Separate Rows]] AND -"Changelog"