SPLIT_PART()
Function Description
SPLIT_PART() is used to break a string into multiple parts based on the given delimiter and return the specified part.
If the specified portion (specified by the unsigned_integer parameter) exceeds the actual number of portions, SPLIT_PART() returns NULL.
SPLIT_PART() will only start counting from left to right, and if unsigned_integer is a negative number, an error will be reported.
Function Syntax
> SPLIT_PART(expr, delimiter, unsigned_integer)
Parameter definition
| Parameters | Description |
|---|---|
| expr | Required parameters. The string to be split. |
| delimiter | Required parameters. A separator used to split strings. |
| unsigned_integer | Required parameters. This is an integer that specifies which part of the string is returned. The first part is 1, the second part is 2, and so on. |
Example
- Example 1
-- Split the string 'axbxc' and use 'x' as the delimiter, the function returns the first part of the string 'axbxc'. So the result of executing this SQL statement is 'a', because the first part after splitting the string 'axbxc' on the basis of the 'x' delimiter is 'a'.
mysql> select split_part('axbxc','x',1);
+------------------------------+
| split_part(axbxc, x, 1) |
+------------------------------+
| a |
+------------------------------+
1 row in set (0.00 sec)
- Example 2
-- Create a new table 't1' which has three columns: 'a' (varchar type), 'b' (varchar type), and 'c' (int type).
create table t1(a varchar,b varchar,c int);
-- Insert multiple rows of data into the 't1' table
insert into t1 values('axbxc','x',1),('axbxcxd','x',2),('axbxcxd','x',3),('axbxcxd','xc',1),('axbxcxd','xc',2),('axbxcxd','xc',1),('axbxcxd','xc',2),('axbxcxd','asas',1),('axbxcxd','x',1),('axxx','x','x',2);
-- Query uses the split_part function to process each row of data in the 't1' table. For each row, it splits the value of the 'a' column into parts (using the value of the 'b' column as the delimiter), and then returns the specified part (specified by the value of the 'c' column). For example, for the first line of data ('axbxc', 'x', 1), it returns 'a' because 'a' is the first part after splitting the string 'axbxc' on the basis of the 'x' separator.
mysql> select split_part(a,b,c) from t1;
+--------------------------+
| split_part(a, b, c) |
+--------------------------+
| a |
| b |
| c |
| axb |
| xd |
| NULL |
| axbxcxd |
| NULL |
| NULL |
| NULL |
| NULL |
| a |
| NULL |
+--------------------------+
13 rows in set (0.01 sec)