PostgreSQL REGEXP_SPLIT_TO_TABLE Function

Summary: In this tutorial, you’ll learn how to use the PostgreSQL REGEXP_SPLIT_TO_TABLE function to split a string into one or more rows based on a regular expression pattern.

Introduction to REGEXP_SPLIT_TO_TABLE Function #

The REGEXP_SPLIT_TO_TABLE function splits a string into multiple rows based on a POSIX regular expression pattern as a delimiter.

Here’s the syntax of the REGEXP_SPLIT_TO_TABLE function:

REGEXP_SPLIT_TO_TABLE(string text, pattern text) -> SETOF textCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Parameters:

• string: The input string you want to split.
• pattern: A POSIX regular expression pattern used as a delimiter for splitting the source string.
• flags (optional): Modifiers that change how the regular expression matches the source string.

The REGEXP_SPLIT_TO_TABLE function returns a set of text values, where each row represents a substring.

If the REGEXP_SPLIT_TO_TABLE function does not find a match for the given regular expression pattern in the input string, it returns the entire input string as a single row.

The REGEXP_SPLIT_TO_TABLE function returns an empty set (no rows) if the input string or regular expression pattern is NULL.

Basic PostgreSQL REGEXP_SPLIT_TO_TABLE Function Examples #

Example 1: Split a string using a comma (“,”) as the delimiter #

The following example uses the REGEXP_SPLIT_TO_TABLE function to split a string using a comma (",") as the delimiter:

SELECT
  REGEXP_SPLIT_TO_TABLE('SQL,Postgres,PostgreSQL', ',') AS result;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

   result
------------
 SQL
 Postgres
 PostgreSQLCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Example 2: Split a string using one or more digits as a delimiter #

The following query uses the REGEXP_SPLIT_TO_TABLE function to split a string using one or more digits as a delimiter:

SELECT
  s
FROM
  REGEXP_SPLIT_TO_TABLE('PostgreSQL 17 Live', '\d+') s;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

      s
-------------
 PostgreSQL
  Live
(2 rows)Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this example, we use the REGEXP_SPLIT_TO_TABLE function in the FROM clause. The query returns rows produced by the REGEXP_SPLIT_TO_TABLE function.

Example 3: No match found #

The following example uses the REGEXP_SPLIT_TO_TABLE function to return the entire input string as a row of the result because there is no match:

SELECT
  REGEXP_SPLIT_TO_TABLE('pgtutorial.com', '\d+') AS result;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

     result
----------------
 pgtutorial.comCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Using Whitespace as a Delimiter #

The following query uses the REGEXP_SPLIT_TO_TABLE function to split a string based on whitespace characters using \s+ as the pattern:

SELECT
  REGEXP_SPLIT_TO_TABLE('PostgreSQL Tutorial', '\s+') AS result;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

   result
------------
 PostgreSQL
 TutorialCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this example, the regular expression \s+ treats one or more spaces as a single delimiter, ensuring the result string has no empty string.

Splitting Based on Multiple Delimiters #

The following example uses the REGEXP_SPLIT_TO_TABLE function with a pattern to split a string on multiple delimiters, including commas (“,”), semicolons (“;”), and spaces (” “):

SELECT
  REGEXP_SPLIT_TO_TABLE('SQL,Postgres,PostgreSQL', '[,; ]+') AS result;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

   result
------------
 SQL
 Postgres
 PostgreSQLCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Handling Special Characters #

If the input string contains special regular expression characters, you must escape it properly.

For example, the following statement uses the REGEXP_SPLIT_TO_TABLE function to split the domain name (www.pgtutorial.com) using the character (.):

SELECT
  REGEXP_SPLIT_TO_TABLE('www.pgtutorial.com', '\.') AS result;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

   result
------------
 www
 pgtutorial
 comCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Since the . is a special regular expression character, we escape it using a backslash (\.).

Using REGEXP_SPLIT_TO_TABLE Function with Table Data #

Step 1: Create a table called posts:

CREATE TABLE posts (
    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    body VARCHAR(200) NOT NULL,
    tags TEXT
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Step 2: Insert some rows into the posts table:

INSERT INTO posts (body, tags)
VALUES
('Learn how REGEXP_SPLIT_TO_TABLE function can split strings efficiently', '#database #postgres #regex'),
('Need to split a string into an array in postgres? Use REGEXP_SPLIT_TO_ARRAY!', '#database #postgres #array');Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Step 3: Split strings in the tags column into multiple rows:

SELECT
  id,
  TRIM(tag) AS tag
FROM
  posts,
  REGEXP_SPLIT_TO_TABLE(tags, '#') AS tag
WHERE
  TRIM(tag) <> '';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

id  | tag
----|--------
1   | database
1   | postgres
1   | regex
2   | database
2   | postgres
2   | arrayCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

How the query works:

• The REGEXP_SPLIT_TO_TABLE(tags, '#') function splits the tags column by #.
• TRIM(tag) removes leading and trailing spaces from the resulting tags.
• The WHERE TRIM(tag) <> '' filter removes empty tags.

Summary #

• Use the REGEXP_SPLIT_TO_TABLE function to split a string into rows based on a POSIX regular expression pattern.