PostgreSQL SPLIT_PART() Function (Multiple Examples)

In PostgreSQL, managing and extracting data from string is one of the common requirements in database operations. We often deal with strings that contain delimited values, PostgreSQL provides various functions to facilitate the data extraction part efficiently. One such function is SPLIT_PART. In this tutorial, we will learn about the syntax, parameters and practical examples of using the SPLIT_PART function in PostgreSQL.

PostgreSQL SPLIT_PART Function

The SPLIT_PART function in PostgreSQL allows users to split a string based on a specified delimiter and extract a specific part of the split string. This function is particularly useful when dealing with data structured in a string. Any letter or character can be accepted, including spaces, commas, and regular expressions.

Syntax:

SPLIT_PART(string, delimeter, part_number)Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Here, ‘string’ represents a string that you want to split into parts. The ‘delimiter’ represents the character or substring used to separate the parts of the string. While the ‘part_number’ represents the index of the part that we want to fetch.

Note: The index must start from 1 and be a positive number.

We are going to use the students table as an example for the rest of this tutorial.

Examples of SPLIT_PART Function

1. Splitting String From a Comma

If we want to split the skills column which contains comma-separated values into separate parts, we can do it using the following query:

SELECT 
name, 
date_admission, 
department, 
course, 
SPLIT_PART(skills, ',', 1) AS skill1,
SPLIT_PART(skills, ',', 2) AS skill2 
FROM students;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

Here, the SPLIT_PART function splits the skills column based on the comma (,) delimiter. The first parameter specifies the column we want to split (skills). The second parameter specifies the delimiter (,). The third parameter specifies the part number we want to fetch after splitting.

Note: If a row contains only one skill or if there’s no comma-separated value in the skills column, the SPLIT_PART function will return an empty string for the second skill.

2. Splitting String From a Hyphen

If we want to split the date_admission column which contains hyphen-separated values into separate parts, we can do it using the following query:

SELECT name,SPLIT_PART(date_admission::TEXT,'-', 1) AS Year,
SPLIT_PART(date_admission::TEXT,'-', 2) AS Month,
SPLIT_PART(date_admission::TEXT,'-', 3) As Day 
FROM students;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

Here, the SPLIT_PART function splits the date_admission column based on the hyphen (-) delimiter. The first parameter specifies the column we want to split (date_admission). The second parameter specifies the delimiter (-). The third parameter specifies the part number we want to fetch after splitting.

Also Read: 5 SQL Functions for String Manipulation (With Examples)

3. Splitting String From a Space

If we want to split the name column which contains space in between the strings, we can do it using the following query:

SELECT name,SPLIT_PART(name,' ', 2) 
FROM students;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

Here, the SPLIT_PART function splits the name column based on the space (‘ ‘) delimiter. The first parameter specifies the column we want to split (name). The second parameter specifies the delimiter (‘ ‘). The third parameter specifies the part number we want to fetch after splitting.

Conclusion

In this tutorial, we have learned about one of the most important tools of PostgreSQL i.e. SPLIT_PART function, a valuable tool for splitting strings and extracting specific parts based on defined delimiters. This tutorial includes the practical usage of the SPLIT_PART function with its syntax and parameters using multiple examples. We hope you have enjoyed this tutorial.

Reference

https://www.postgresqltutorial.com/postgresql-string-functions/postgresql-split_part/