PostgreSQL – SPLIT_PART Function

Last Updated : 08 Nov, 2024

The PostgreSQL SPLIT_PART() function is a powerful tool for splitting strings based on a specific delimiter, returning a specified part of the string. This function is particularly useful when working with structured data in text format, such as CSV values or delimited dates, and enables efficient data extraction and manipulation in SQL queries.

In this article, we’ll explain the syntax and practical use cases of the SPLIT_PART function in PostgreSQL, illustrating its utility with examples. Let’s go deep into how to use SPLIT_PART to enhance our data-handling skills.

What is PostgreSQL SPLIT_PART Function?

The SPLIT_PART function in PostgreSQL is designed to split a text string into parts based on a specified delimiter and then retrieve a specific part by its position. This function is especially useful for extracting structured data, like individual date components or elements from CSV-like fields, in a simple, readable way. By using SPLIT_PART, we can easily break down and access parts of a string for data extraction and transformation tasks.

Syntax

SPLIT_PART(string, delimiter, position)

Key Terms

String Argument: The string argument is the input string that you want to split.
Delimiter: The delimiter is a string used to define the points at which the input string should be split.
Position: The position argument specifies which part of the split string should be returned. It must be a positive integer, with 1 representing the first substring.

Key Benefits of Using SPLIT_PART in PostgreSQL

Efficient Data Extraction: Ideal for breaking down complex strings.
Improves Query Readability: Simplifies SQL queries by reducing the need for complex string manipulations.
Versatile Application: Can be used across SELECT, WHERE, and other clauses to make queries more dynamic.

PostgreSQL SPLIT_PART Function Examples

Let us take a look at some of the examples of the SPLIT_PART Function in PostgreSQL to better understand how this function can simplify string manipulation and data extraction tasks.

Example 1: Extracting Year and Month from Payment Date

The below query uses the SPLIT_PART() function to return the year and month of the ‘payment_date’ from the ‘payment’ table of the sample database, ie, dvdrental:

Sample Database

Query:

SELECT
    split_part(payment_date::TEXT, '-', 1) y,
    split_part(payment_date::TEXT, '-', 2) m,
    amount
FROM
    payment;

Output

PostgreSQL SPLIT_PART Function Example

Explanation:

The query returns the year and month along with the payment amount for each record in the ‘payment' table.

'payment_date::TEXT' converts the ‘payment_date' to a text string.
'SPLIT_PART(payment_date::TEXT, '-', 1)' extracts the year (the first part of the date).
'SPLIT_PART(payment_date::TEXT, '-', 2)' extracts the month (the second part of the date).

Example 2: Splitting a Comma-Separated String

Through the below query the string ‘A, B, C’ is split on the comma delimiter (, ) and results in 3 substrings: ‘A’, ‘B’, and ‘C’. Because the position is 2, the function returns the 2^nd substring which is ‘B’:

Query:

SELECT SPLIT_PART('A, B, C', ', ', 2);

Output

PostgreSQL SPLIT_PART Function Example

Explanation:

The input string 'A, B, C' is split into three substrings: 'A', 'B', and 'C'.
The function returns the second substring, which is 'B'.

Important Points About PostgreSQL SPLIT_PART Function

If the specified position exceeds the number of available substrings, the function returns an empty string.
Consider edge cases, such as strings without the delimiter or strings where the delimiter appears multiple times consecutively.
The position argument must be a positive integer. If the position is less than 1, PostgreSQL will return an error.
The function can implicitly convert other data types to text. For instance, a date can be converted to text using ‘::TEXT' to use with SPLIT_PART().

Conclusion

The SPLIT_PART function in PostgreSQL is essential for splitting and extracting parts of strings, making it highly effective in scenarios where structured text needs to be parsed and analyzed. From splitting dates to extracting domains from email addresses, SPLIT_PART provides flexibility and simplicity in data manipulation.

FAQs

What is the Split_part function in Postgres?

The SPLIT_PART function in PostgreSQL allows you to split a string into parts based on a specified delimiter and returns the nth part. Its syntax is SPLIT_PART(string, delimiter, field_number), making it useful for extracting specific segments from structured text.

What is the difference between split and Split_part?

In PostgreSQL, SPLIT_PART is a built-in function that extracts a specified part of a string using a delimiter, whereas split usually refers to string-splitting methods in programming languages that divide text into arrays or lists of substrings based on a delimiter.

What is the Split_part function in SQL Server?

SQL Server does not have a direct SPLIT_PART function. However, similar functionality can be achieved using the STRING_SPLIT function (which returns a table of values) or by combining SUBSTRING and CHARINDEX functions to extract specific parts of a string based on a delimiter