String split column values in PostgreSQL – SPLIT_PART()

In PostgreSQL, the split_part() function is used to split a string on a specified delimiter and return a specific part of the split string. split_part() directly returns the desired part as a string. String split column values using SPLIT_PART()

Syntax of SPLIT_PART() in PostgreSQL:

• string: The string to be split.
• delimiter: The delimiter string used for splitting.
• field: The position of the part to be returned (1-based index). 1^st part, 2^nd part etc

Examples of String split column values in PostgreSQL

Example 1: Splitting a String and Getting a Specific Part

To split the string ‘SQL,Python,PostgreSQL’ on the comma (,) and get the third part:

 

SELECT SPLIT_PART('SQL,Python,PostgreSQL', ',', 3) AS part;

Output:

Example 2: String Split a Column Values in PostgreSQL Table

We will be using employees table.

Let’s extract first name from “ename” column of employees table using SPLIT_PART() function.

 

SELECT *, SPLIT_PART(ename, ' ', 1) AS first_name
FROM employees;

Argument 1 in split_part() function indicates First part, So will get first name

Output:

Let’s extract second name from “ename” column of employees table using SPLIT_PART() function.

 

SELECT *, SPLIT_PART(ename, ' ', 2) AS second_name
FROM employees;

Argument 2 in split_part() function indicates second part, So will get last name

Output:

Let’s extract both first name and second name from “ename” column of employees table using SPLIT_PART() function.

 

select *,split_part(ename,' ',1) as first_name, split_part(ename,' ',2) as second_name
from employees

We have splitted ename column into first_name and last_name, So the resultant table will be

Output:

Example 3: Splitting a Column’s Values in PostgreSQL Table

We will be using  employees_email table

Let’s extract both sides of the delimiter “@” from “email” column of employees_email  table using SPLIT_PART() function.

 

select *,split_part(email,'@',1) as emailname, split_part(email,'@',2) as domain_name 
from employees_email

So the resultant table will be

Output:

Summary :

The split_part() function in PostgreSQL is a powerful tool for extracting specific parts of a string based on a delimiter. It allows you to directly access the desired part of the string