MySQL | SUBSTRING_INDEX method
Start your free 7-days trial now!
MySQL's SUBSTRING_INDEX(~) method returns the substring before the count occurrence of a particular delimiter delim in the input source string.
Parameters
1. str | string
The string to return substring from.
2. delim | string
The string that should serve as the delimiter.
The search for delim in the string str is case-sensitive.
3. count | integer
Positive integer: return the substring left of the count occurrence of the delimiter (counting from left).
Negative integer: return the substring right of the count occurrence of the delimiter (counting from the right).
Return value
Case | Return value |
|---|---|
| The source string |
| Substring left of the |
| Substring right of the |
Examples
Basic usage
To return the substring left of the first occurrence of 'sky':
SELECT SUBSTRING_INDEX('www.skytowner.com', 'sky', 1);
+------------------------------------------------+| SUBSTRING_INDEX('www.skytowner.com', 'sky', 1) |+------------------------------------------------+| www. |+------------------------------------------------+
To return the substring left of the first occurrence of 'sky':
SELECT SUBSTRING_INDEX('www.skytowner.com', 'SKY', 1);
+------------------------------------------------+| SUBSTRING_INDEX('www.skytowner.com', 'SKY', 1) |+------------------------------------------------+| www.skytowner.com |+------------------------------------------------+
No match is found when there is a case-insensitive match between delimiter and source string:
Count parameter
To return the substring left of the 2nd '.' (counting from the left):
SELECT SUBSTRING_INDEX('www.skytowner.com', '.', 2);
+----------------------------------------------+| SUBSTRING_INDEX('www.skytowner.com', '.', 2) |+----------------------------------------------+| www.skytowner |+----------------------------------------------+
To return the substring right of the 2nd '.' (counting from the right):
SELECT SUBSTRING_INDEX('www.skytowner.com', '.', -2);
+-----------------------------------------------+| SUBSTRING_INDEX('www.skytowner.com', '.', -2) |+-----------------------------------------------+| skytowner.com |+-----------------------------------------------+
