The SPLIT_PART() function is used to split strings by character. Here is code example:
SELECT SPLIT_PART('Educ@tive', '@', 2) AS character;
How to use the SPLIT_PART() function in SQL
Key takeaways:
SPLIT_PART()is a powerful function for string manipulation in SQL. It splits a string based on a delimiter and returns a specific part.Syntax:
SPLIT_PART(string, delimiter, position)
stringis the string to split.
delimiteris the character or substring to split the string.
positionis the index of the substring to return (starting from 1).The
SPLIT_PART()method returns an empty string if the position exceeds the number of parts, and it’s crucial to choose a delimiter that does not appear in the actual data to avoid incorrect splits. Additionally, the position must be a positive integer; negative values will result in an error.
Manipulating strings efficiently is a critical skill in database management and SQL. Assume we’re analyzing user’s comments collected in a single string, where each piece of comment is separated by a comma. We need a function that splits this string into individual comment entries, so we can systematically evaluate each comment and gain actionable insights.
"Without a systematic way to start and keep data clean, bad data will happen." — Donato Diorio
One powerful function for handling string data is the SPLIT_PART() function. In this Answer, we’ll understand SPLIT_PART() function with code examples.
SQL SPLIT_PART() function
The SPLIT_PART() function splits a specific string based on the specified delimiter and extracts the appropriate string. The function starts from the left of the given string.
Syntax
SPLIT_PART(string, delimiter, position)
Parameters
stringrepresents the string to split.delimiterrepresents a substring or a character used for splitting.positionspecifies the position of the part to be returned from the string. It takes a positive integer value, and the default is1.
Points to remember for the SPLIT_PART() function
- The
SPLIT_PART()function will return an empty string if the specified position value exceeds the number of parts after splitting. - The
positionparameter should be positive. If it’s negative, PostgreSQL will return an error. - The
positionparameter index starts at 1, not 0. Use thedelimiterparameter carefully to avoid conflicts with the actual data. If the delimiter is part of the actual data, the data might split incorrectly.
Code example: Splitting comma-separated comments
The following code demonstrates how to use the SPLIT_PART() function to split comma-separated comments in data.
-- QuerySELECT SPLIT_PART('good movie, actor name, 5/5 rating, worst movie', ',' , 3) AS comment
Code explanation
Line 2: We use the
SPLIT_PART()function and split the comments using the comma between each comment as the delimiter to create a new column calledcomment.
Code example: Splitting a student’s name
The following code demonstrates how to split a student’s name from the name column of the Student table using the SPLIT_PART() function.
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
- Lines 1–7: We create the table named
Studentwith columnsid,name,level,gender, andstate. - Lines 10–21: We insert data into the
Studenttable. - Lines 24–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.
In summary, the SPLIT_PART() function is a useful tool for efficiently processing and analyzing delimited string data in SQL. By leveraging this function, we can simplify SQL data manipulation tasks and gain meaningful insights from even the most complex data strings.
Frequently asked questions
Haven’t found what you were looking for? Contact Us
How do we SQL split string by character?
Can the SPLIT_PART() function handle multiple delimiters?
How can we split a string into multiple rows in SQL?
TRENDING TOPICS
