How to use the SPLIT_PART() function in SQL
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 is1.
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
Studentwith columnsid,name,level,gender, andstate. -
Lines 10 to 21: We insert data into the
Studenttable. -
Lines 24 to 26: We use the
SPLIT_PART()function and split thenamecolumn using the space between eachnameas the delimiter to create a new column calledfirst_name.
Note: The function returns an empty string if the position is
2.
RELATED TAGS
CONTRIBUTOR
View all Courses
SOLUTIONS
SOLUTIONS
SOLUTIONS
