substring_index

Name

SinceVersion 1.2

SUBSTRING_INDEX

description

Syntax

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

Split content to two parts at position where the fields of delimiter stays, return one of them according to below rules: if field is positive, return the left part; else if field is negative, return the right part; if field is zero, return an empty string when content is not null, else will return null.

• delimiter is case sensitive and multi-byte safe.
• delimiter and field parameter should be constant.

example

1. mysql> select substring_index("hello world", " ", 1);
2. +----------------------------------------+
3. | substring_index("hello world", " ", 1) |
4. +----------------------------------------+
5. | hello                                  |
6. +----------------------------------------+
7. mysql> select substring_index("hello world", " ", 2);
8. +----------------------------------------+
9. | substring_index("hello world", " ", 2) |
10. +----------------------------------------+
11. | hello world                            |
12. +----------------------------------------+
13. mysql> select substring_index("hello world", " ", -1);
14. +-----------------------------------------+
15. | substring_index("hello world", " ", -1) |
16. +-----------------------------------------+
17. | world                                   |
18. +-----------------------------------------+
19. mysql> select substring_index("hello world", " ", -2);
20. +-----------------------------------------+
21. | substring_index("hello world", " ", -2) |
22. +-----------------------------------------+
23. | hello world                             |
24. +-----------------------------------------+
25. mysql> select substring_index("hello world", " ", -3);
26. +-----------------------------------------+
27. | substring_index("hello world", " ", -3) |
28. +-----------------------------------------+
29. | hello world                             |
30. +-----------------------------------------+
31. mysql> select substring_index("hello world", " ", 0);
32. +----------------------------------------+
33. | substring_index("hello world", " ", 0) |
34. +----------------------------------------+
35. |                                        |
36. +----------------------------------------+

keywords

1. SUBSTRING_INDEX, SUBSTRING