PostgreSQL – SPLIT_PART Function

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:

Query:

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

Output

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

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.