intro
Let's explore five different functions that PostgreSQL exposes to split a string column into an array or table of substrings.
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:
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():
1
SPLIT_PART(string, delimiter, position)
As you can see, the PostgreSQL function requires three arguments:
Note that when n is 0 SPLIT_PART() raises the following error:
ERROR: field position must not be zero
Consider the example below:
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:
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():
1
STRING_TO_ARRAY(string, delimiter[, null_string])
The function accepts three arguments:
Take a look at the following example:
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:
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:
1
STRING_TO_TABLE(string, delimiter[, null_string])
The arguments have the same meaning as in STRING_TO_ARRAY().
Check out the query below:
1
SELECT STRING_TO_TABLE('a#b#c', '#');
This will produce the following 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:
1
REGEXP_SPLIT_TO_ARRAY(string, delimeter[, flags ])
The arguments it accepts are:
See the example that follows:
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:
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:
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:
1
SELECT REGEXP_SPLIT_TO_TABLE('Hello World!', '\s+');
This SQL query will produce the table below:
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:
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:
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.
