PostgreSQL – SPLIT_PART Function

The PostgreSQL SPLIT_PART() function is used to split a string from a specific delimiter and these queries return the n^th substring.

Syntax: SPLIT_PART(string, delimiter, position)

Let’s analyze the above syntax:

• The string argument is the string to be split.
• The delimiter is a string used as the delimiter for splitting.
• The position argument sets the part of the string that is to be returned, starting from 1. The argument must have a positive integer as its value.

Example 1:

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:

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

Output:

Example 2:

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’:

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

Output: