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.
SPLIT_PART(string,delimiter, position)
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.
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;
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
CONTRIBUTOR
View all Courses