POSTGRESQL
Split

5 Ways to Split a String in PostgreSQL

intro

Let's explore five different functions that PostgreSQL exposes to split a string column into an array or table of substrings.

Tools used in the tutorial
Tool Description Link
Dbvisualizer DBVISUALIZER
TOP RATED DATABASE MANAGEMENT TOOL AND SQL CLIENT
PostgreSQL logo POSTGRESQL
THE POSTGRESQL DATABASE

Splitting a string involves breaking a single string of text into smaller parts or substrings based on a delimiter or pattern. The idea behind this operation is to extract, manipulate, or organize the information contained in a text column. This is such a common operation that there are several Postgres split string functions.

In this tutorial, you will see why splitting a string in PostgreSQL is important and how to do it with built-in Postgres functions.

Postgres Split String Operation: Why and When

Splitting strings is an essential operation in PostgreSQL. When dealing with textual data, there are numerous scenarios where you need to extract specific substrings from an existing string. Some examples are:

  • Extracting specific words from text: You may be interested in isolating words in specific formats, such as e-mail or URLs.
  • Breaking down large strings into smaller parts: This makes it easier to achieve more precise filtering, sorting, and indexing.
  • Retrieving original data from a string in a particular format: Storing structured data in a string and then retrieving its original elements. For example, retrieve the elements of a list of strings separated by commas.

These scenarios are so popular that knowing how to perform a Postgres split string operation is crucial.

How to Split a String in Postgres

Let's now see five different ways to approach the Postgres split string operation.

To better understand the results produced by the functions offered by PostgreSQL to split a string, we will look at some real-world examples in DbVisualizer, a powerful and complete PostgreSQL database client.

Approach #1: SPLIT_PART()

The SPLIT_PART() function splits a string based on a specified delimiter and returns the nth sub-string (counting from 1). When n is negative, it returns the nth-from-last sub-string. Specifically, this is the syntax of SPLIT_PART():

Copy
        
1 SPLIT_PART(string, delimiter, position)

As you can see, the PostgreSQL function requires three arguments:

  • string: The string to split.
  • delimiter: The string used as the delimiter for splitting.
  • position: The index (starting from 1) of the part to return. If position is greater than the number of parts generated after splitting, the function returns an empty string.

Note that when n is 0 SPLIT_PART() raises the following error:

ERROR: field position must not be zero

Consider the example below:

Copy
        
1 SPLIT_PART('a#b#c', '#', 2)

This will split a#b#c into ('a', 'b', 'c') and select the second element, which is b.

See some SPLIT_PART() scenarios below:

SPLIT_PART() in action in DbVisualizer
SPLIT_PART() in action in DbVisualizer

Approach #2: STRING_TO_ARRAY()

STRING_TO_ARRAY() splits a string at the occurrences of a delimiter and returns an array. As the name suggests, that is the function to perform the Postgres split string to array conversion.

In detail, this is the syntax of STRING_TO_ARRAY():

Copy
        
1 STRING_TO_ARRAY(string, delimiter[, null_string])

The function accepts three arguments:

  • string: The string to be split.
  • delimiter: The string used to decide how to do the split. When if is NULL, each character in string will become a separate element in the array. When it is an empty string, the function will return an array with a single element containing string.
  • null_string: If supplied and is not NULL, fields in the array matching this string are replaced by NULL.

Take a look at the following example:

Copy
        
1 STRING_TO_ARRAY('a#b#c', '#')

The function will split the a#b#c string into the {'a', 'b', 'c'} array.

Explore common STRING_TO_ARRAY() scenarios below:

STRING_TO_ARRAY() in action in DbVisualizer
STRING_TO_ARRAY() in action in DbVisualizer

Approach 3: STRING_TO_TABLE()

STRING_TO_TABLE() works just like STRING_TO_ARRAY() but instead of returning an array, it produces a set of rows. In other words, it generates a tabular result in which each sub-string represents a row of a table.

This is what the syntax of STRING_TO_TABLE() looks like:

Copy
        
1 STRING_TO_TABLE(string, delimiter[, null_string])

The arguments have the same meaning as in STRING_TO_ARRAY().

Check out the query below:

Copy
        
1 SELECT STRING_TO_TABLE('a#b#c', '#');

This will produce the following table:

Note that the result of STRING_TO_TABLE() is a table
Note that the result of STRING_TO_TABLE() is a table

Approach 4: REGEXP_SPLIT_TO_ARRAY()

REGEXP_SPLIT_TO_ARRAY() splits a string using a POSIX regular expression as the delimiter, producing an array as a result. Here is what the syntax of the function looks like:

Copy
        
1 REGEXP_SPLIT_TO_ARRAY(string, delimeter[, flags ])

The arguments it accepts are:

  • string: The text to be split.
  • delimeter: A POSIX regular expression to apply to string for splitting purposes.
  • flags: An optional string containing the flags accepted by POSIX regular expression in PostgreSQL. Learn more in the docs.

See the example that follows:

Copy
        
1 REGEXP_SPLIT_TO_ARRAY('Hello World!', '\s+');

That will split the string based on spaces and returns {'Hello', 'World!'}.

See common REGEXP_SPLIT_TO_ARRAY() use cases in the image below:

REGEXP_SPLIT_TO_ARRAY() in action in DbVisualizer
REGEXP_SPLIT_TO_ARRAY() in action in DbVisualizer

Approach 5: REGEXP_SPLIT_TO_TABLE()

REGEXP_SPLIT_TO_TABLE() works just like REGEXP_SPLIT_TO_ARRAY(). The main difference is that it returns a table instead of an array. The syntax of the function is:

Copy
        
1 REGEXP_SPLIT_TO_TABLE(string, delimeter[, flags ])

The arguments have the same meaning as in REGEXP_SPLIT_TO_ARRAY().

Check out the following query:

Copy
        
1 SELECT REGEXP_SPLIT_TO_TABLE('Hello World!', '\s+');

This SQL query will produce the table below:

Note the tabular result
Note the tabular result

Et voilà! You just saw all split string Postgres functions!

Conclusion

In this article, you learned why and when it is common to split a string in PostgreSQL. In detail, you saw the five different functions Postgres offers for dividing text into smaller parts based on a delimiter. You are now a Postgres split string expert.

Dealing with string splits becomes easier when you can visually see the result of your queries. This is where DbVisualizer comes into play! In addition to supporting all PostgreSQL features, this tool is compatible with dozens of DBMSs and also offers drag-and-drop query construction functionality, top-notch query optimization capabilities, and much more. Manage your databases like a pro. Download DbVisualizer for free now!

FAQ

What are the functions available to split a string in PostgreSQL?

There are a few ways to split a string in PostgreSQL. The most popular functions are:

  • SPLIT_PART()
  • STRING_TO_ARRAY()
  • STRING_TO_TABLE()
  • REGEXP_SPLIT_TO_ARRAY()
  • REGEXP_SPLIT_TO_TABLE()

Is there a built-in function for performing a PostgreSQL split string operation using a regular expression?

Yes, PostgreSQL offers two functions to split a string using a regular expression:

  1. REGEXP_SPLIT_TO_ARRAY(): Splits a string into an array with a regex.
  2. REGEXP_SPLIT_TO_TABLE(): Splits a string into a table with a regex.

What is the difference between the STRING_TO_ARRAY() and REGEXP_SPLIT_TO_STRING() functions for splitting strings in PostgreSQL?

Both are Postgres split string functions that return an array. The main difference is that STRING_TO_ARRAY() accepts a string delimiter, while REGEXP_SPLIT_TO_STRING() expects a regex delimiter.

Can I split a string into multiple rows in PostgreSQL?

Yes, you can! Both SPLIT_TO_TABLE() and REGEXP_SPLIT_TO_TABLE() partition a string into a table that contains a row for each part the string has been split into.

What is the performance impact of splitting large strings in PostgreSQL?

Splitting large strings in Postgres can have a performance impact. It may lead to increased memory usage and slower query execution, especially if performed on very long or numerous strings. To get good performance, you may need to optimize your query. Check out our guide on how to work with SQL query optimization.

Dbvis download link img
About the author
Antonello Zanini

Antonello is a software engineer, and often refers to himself as a technology bishop. His mission is to spread knowledge through writing.

The Table Icon
Sign up to receive The Table's roundup
More from the table
Title Author Tags Length Published
title

SQL Alias: Everything You Need to Know About AS in SQL

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 7 min 2024-07-15
title

How to Compare SQL Dates

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 4 min 2024-07-11
title

REGEXP_MATCH SQL Function: The Complete PostgreSQL Guide

author Antonello Zanini tags POSTGRESQL REGEXP SQL 8 min 2024-07-04
title

Primary Key vs. Foreign Key: A Complete Comparison

author Lukas Vileikis tags MySQL POSTGRESQL SQL SQL SERVER 7 min 2024-06-20
title

A Complete Guide to the SQL LIKE Operator

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 8 min 2024-06-17
title

A Definitive Guide to Postgres Foreign Key

author Leslie S. Gyamfi tags POSTGRESQL SQL 12 min 2024-06-13
title

A Guide to the SQL Date Data Types

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 10 min 2024-05-30
title

SQL TRIM: Removing Extra Space Characters From a String

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 6 min 2024-05-16
title

PostgreSQL Full Text Search: The Definitive Guide

author Antonello Zanini tags POSTGRESQL Search 11 min 2024-05-13
title

SQL UNION ALL: Keeping Duplicates When Combining Result Sets

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL UNION 8 min 2024-05-09

The content provided on dbvis.com/thetable, including but not limited to code and examples, is intended for educational and informational purposes only. We do not make any warranties or representations of any kind. Read more here.

Cookie policy

We use cookies to ensure that we give you the best experience on our website. However you can change your cookie settings at any time in your browser settings. Please find our cookie policy here ↗