PostgreSQL REGEXP_SPLIT_TO_ARRAY Function

Summary: in this tutorial, you’ll learn how to use the PostgreSQL REGEXP_SPLIT_TO_ARRAY function to split a string into an array of text based on a POSIX regular expression as a delimiter.

PostgreSQL REGEXP_SPLIT_TO_ARRAY Function Overview #

The REGEXP_SPLIT_TO_ARRAY function splits a string into an array based on a POSIX regular expression as a delimiter.

Here’s the syntax of the REGEXP_SPLIT_TO_ARRAY function:

REGEXP_SPLIT_TO_ARRAY(source, pattern [, flags])Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Parameters:

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

The REGEXP_SPLIT_TO_ARRAY function returns an array of text, where each element represents a substring from the source string split by a POSIX regular expression.

If there is no match, the REGEXP_SPLIT_TO_ARRAY function does not split and returns an array containing the source string as an element.

Basic PostgreSQL REGEXP_SPLIT_TO_ARRAY Function Example #

The following example splits a string using a comma (“,”) as the delimiter:

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

Try it

Output:

          result
---------------------------
 {SQL,Postgres,PostgreSQL}Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

If you need to transform an array into separate rows, you can combine the REGEXP_SPLIT_TO_ARRAY function with the UNNEST() function:

SELECT
  UNNEST(
    REGEXP_SPLIT_TO_ARRAY('SQL,Postgres,PostgreSQL', ',')
  ) 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)

In this example:

• The REGEXP_SPLIT_TO_ARRAY function splits the string into an array of substrings.
• The UNNEST function converts the array to separate rows.

Using Whitespace as a Delimiter #

The following example splits a string based on whitespace characters using \s+ as the pattern:

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

Try it

Output:

        result
-----------------------
 {PostgreSQL,Tutorial}Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this example, \s+ treats one or more spaces as a single delimiter, ensuring we don’t get an empty string in the resulting array.

Splitting Based on Multiple Delimiters #

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

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

Try it

Output:

          result
---------------------------
 {SQL,Postgres,PostgreSQL}Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Handling Special Characters #

If the input string contains special regex characters, you need to escape them properly. For example, to split a domain name (www.pgtutorial.com) using the dot (.):

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

Try it

Output:

        result
----------------------
 {www,pgtutorial,com}Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Since . is a special regex character, we escape it using double backslashes (\\.).

Handling Empty Elements #

If the input string contains consecutive delimiters, the result will include empty elements:

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

Try it

Output:

            result
------------------------------
 {SQL,Postgres,"",PostgreSQL}Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

To remove empty elements from the resulting array, use the ARRAY_REMOVE() function:

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

Try it

Output:

          result
---------------------------
 {SQL,Postgres,PostgreSQL}Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Using REGEXP_SPLIT_TO_ARRAY Function with Table Data #

We’ll use the REGEXP_SPLIT_TO_ARRAY function to split product names from the products table:

The following example uses the REGEXP_SPLIT_TO_ARRAY function to split product names:

SELECT
  product_name,
  (REGEXP_SPLIT_TO_ARRAY(product_name, '\s+')) [2] product_line
FROM
  products
ORDER BY
  product_name;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output

        product_name        | product_line
----------------------------+--------------
 Apple AirPods Pro 3        | AirPods
 Apple iMac 24"             | iMac
 Apple iPad Pro 12.9        | iPad
 Apple iPhone 15            | iPhone
 Apple iPhone 15 Pro Max    | iPhone
 Apple Watch Series 9       | Watch
 Bose SoundLink Max         | SoundLink
...Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Summary #

• Use the REGEXP_SPLIT_TO_ARRAY function to split a string into an array of text based on a POSIX regular expression as a delimiter.
• Use the UNNEST() function to convert arrays into rows.