PostgreSQL – SPLIT_PART Function
The PostgreSQL SPLIT_PART() function is used to split a string from a specific delimiter and these queries return the nth 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.
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;
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 2nd substring which is ‘B’:
SELECT SPLIT_PART('A, B, C', ', ', 2);