split_part

Description

Syntax

VARCHAR split_part(VARCHAR content, VARCHAR delimiter, INT field)

Returns the specified partition by splitting the string according to the delimiter. If field is positive, splitting and counting from the beginning of content, otherwise from the ending.

delimiter and field parameter should be constant.

example

  1. mysql> select split_part("hello world", " ", 1);
  2. +----------------------------------+
  3. | split_part('hello world', ' ', 1) |
  4. +----------------------------------+
  5. | hello                            |
  6. +----------------------------------+
  9. mysql> select split_part("hello world", " ", 2);
  10. +----------------------------------+
  11. | split_part('hello world', ' ', 2) |
  12. +----------------------------------+
  13. | world                             |
  14. +----------------------------------+
  16. mysql> select split_part("2019年7月8号", "月", 1);
  17. +-----------------------------------------+
  18. | split_part('2019年7月8号', '月', 1)     |
  19. +-----------------------------------------+
  20. | 2019年7                                 |
  21. +-----------------------------------------+
  23. mysql> select split_part("abca", "a", 1);
  24. +----------------------------+
  25. | split_part('abca', 'a', 1) |
  26. +----------------------------+
  27. |                            |
  28. +----------------------------+
  30. mysql> select split_part("prefix_string", "_", -1);
  31. +--------------------------------------+
  32. | split_part('prefix_string', '_', -1) |
  33. +--------------------------------------+
  34. | string                               |
  35. +--------------------------------------+
  38. mysql> select split_part("prefix_string", "_", -2);
  39. +--------------------------------------+
  40. | split_part('prefix_string', '_', -2) |
  41. +--------------------------------------+
  42. | prefix                               |
  43. +--------------------------------------+
  45. mysql> select split_part("abc##123###234", "##", -1);
  46. +----------------------------------------+
  47. | split_part('abc##123###234', '##', -1) |
  48. +----------------------------------------+
  49. | 234                                    |
  50. +----------------------------------------+
  52. mysql> select split_part("abc##123###234", "##", -2);
  53. +----------------------------------------+
  54. | split_part('abc##123###234', '##', -2) |
  55. +----------------------------------------+
  56. | 123#                                   |
  57. +----------------------------------------+

keywords

  1. SPLIT_PART,SPLIT,PART