SPLIT_PART()

Description

The SPLIT_PART() function is used to split a string into multiple parts based on a given delimiter and returns the specified part.

If the specified part (designated by the unsigned_integer parameter) exceeds the actual number of existing parts, SPLIT_PART() will return NULL.

SPLIT_PART() counts parts from left to right only. If unsigned_integer is a negative number, an error will occur.

Syntax

  1. > SPLIT_PART(expr, delimiter, unsigned_integer)

Arguments

ArgumentsDescription
exprRequired. The string to be split.
delimiterRequired. The delimiter used to split the string.
unsigned_integerRequired. This is an integer specifying which part of the string to return. The first part is 1, the second part is 2, and so on.

Examples

  • Example 1
  1. -- Split the string 'axbxc' and use 'x' as the delimiter. This function will return the first part of the string 'axbxc'. Therefore, the result of executing this SQL statement is 'a' because 'a' is the first part of the string 'axbxc' split on the basis of the 'x' delimiter.
  2. mysql> select split_part('axbxc','x',1);
  3. +-------------------------+
  4. | split_part(axbxc, x, 1) |
  5. +-------------------------+
  6. | a                       |
  7. +-------------------------+
  8. 1 row in set (0.00 sec)
  • Example 2
  1. -- Create a new table 't1' with three columns: 'a' (varchar type), 'b' (varchar type), and 'c' (int type).
  2. create table t1(a varchar,b varchar,c int);
  3. -- Insert multiple rows of data into the 't1' table
  4. insert into t1 values('axbxc','x',1),('axbxcxd','x',2),('axbxcxd','x',3),('axbxcxd','xc',1),('axbxcxd','xc',2),('axbxcxd','xc',3),('axbxcxd','asas',1),('axbxcxd','asas',2),(null,'asas',3),('axbxcxd',null,3),('axbxcxd','asas',null),('axxx','x',1),('axxx','x',2);
  5. -- Query uses the split_part function to process each row in the 't1' table. For each row, it splits the value of the 'a' column into multiple parts (using the value of the 'b' column as the delimiter) and then returns the specified part (designated by the value of the 'c' column). For example, for the first row of data ('axbxc', 'x', 1), it returns 'a' because 'a' is the first part of the string 'axbxc' split on the basis of the 'x' delimiter.
  6. mysql> select split_part(a,b,c) from t1;
  7. +---------------------+
  8. | split_part(a, b, c) |
  9. +---------------------+
  10. | a                   |
  11. | b                   |
  12. | c                   |
  13. | axb                 |
  14. | xd                  |
  15. | NULL                |
  16. | axbxcxd             |
  17. | NULL                |
  18. | NULL                |
  19. | NULL                |
  20. | NULL                |
  21. | a                   |
  22. | NULL                |
  23. +---------------------+
  24. 13 rows in set (0.01 sec)