Year-End Discount: 10% OFF 1-year and 20% OFF 2-year subscriptions!

Trusted answers to developer questions
Trusted Answers to Developer Questions

Related Tags

sql
postgresql

How to use the SPLIT_PART() function in SQL

Maria Elijah

Tired of LeetCode? 😩

Learn the 24 patterns to solve any coding interview question without getting lost in a maze of LeetCode-style practice problems. Practice your skills in a hands-on, setup-free coding environment. πŸ’ͺ

Overview

The SPLIT_PART() function splits a specific string based on the specified delimiter and returns the appropriate string. The function starts from the left of the given string.

Syntax

SPLIT_PART(string,delimiter, position)

Parameters

  • string: This represents the string to be split.
  • delimiter: This represents a string or character used for splitting.
  • position: This specifies the position of the part to be returned from the string. It takes a positive integer value, and the default is 1.

Note: The SPLIT_PART() function will return an empty string if the specified position value exceeds the number of parts after splitting.

Code example

The following code will demonstrate how to use the SPLIT_PART() function in SQL:

CREATE TABLE Student (
id int,
name varchar(50),
level int,
gender varchar(10),
state varchar(15)
);
-- Insert data
INSERT INTO Student
VALUES (02,'Paul Dons',100,'M','Lagos');
INSERT INTO Student
VALUES (03,'Ameera Abedayo',300,'F','Imo');
INSERT INTO Student
VALUES (04,'Maria Elijah',200,'F','Lagos');
INSERT INTO Student
VALUES (05,'David Hassan',500,'M','Abuja');
INSERT INTO Student
VALUES (06,'Niniola Disu',100,'F','Lagos');
INSERT INTO Student
VALUES (08,'Joe Smith',100, 'M','Lagos');
-- Query
SELECT name, SPLIT_PART(name, ' ', 1) AS first_name
FROM Student
ORDER BY id;

Code explanation

In the code above:

  • Lines 1 to 7: We create the table named Student with columns id, name, level, gender, and state.

  • Lines 10 to 21: We insert data into the Student table.

  • Lines 24 to 26: We use the SPLIT_PART() function and split the name column using the space between each name as the delimiter to create a new column called first_name.

Note: The function returns an empty string if the position is 2.

RELATED TAGS

sql
postgresql

Tired of LeetCode? 😩

Learn the 24 patterns to solve any coding interview question without getting lost in a maze of LeetCode-style practice problems. Practice your skills in a hands-on, setup-free coding environment. πŸ’ͺ

Keep Exploring
Related Courses