How to use the SPLIT_PART() function in SQL
Maria ElijahTired 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 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 dataINSERT INTO StudentVALUES (02,'Paul Dons',100,'M','Lagos');INSERT INTO StudentVALUES (03,'Ameera Abedayo',300,'F','Imo');INSERT INTO StudentVALUES (04,'Maria Elijah',200,'F','Lagos');INSERT INTO StudentVALUES (05,'David Hassan',500,'M','Abuja');INSERT INTO StudentVALUES (06,'Niniola Disu',100,'F','Lagos');INSERT INTO StudentVALUES (08,'Joe Smith',100, 'M','Lagos');-- QuerySELECT name, SPLIT_PART(name, ' ', 1) AS first_nameFROM StudentORDER 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
Maria ElijahTired 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. 💪
