MySQL SUBSTRING_INDEX() Function
The SUBSTRING_INDEX() function in MySQL extracts a portion of a string, based on a delimiter and the number of times that delimiter appears. It provides a flexible way to work with strings that have separators, allowing you to extract substrings from either the left or right side of the delimiter.
SUBSTRING_INDEX(): Definition and Usage
SUBSTRING_INDEX() is particularly helpful for parsing strings with multiple delimiters. You can specify how many times to look for the delimiter, and the function returns all the text either to the left or right of the specified number of occurrences of the delimiter.
Syntax
Syntax
SUBSTRING_INDEX(string, delimiter, number)
Parameter Values
| Parameter | Description |
|---|---|
string |
The original string. This is required. |
delimiter |
The delimiter character to use for splitting the string. This is required. |
number |
The number of times to search for the delimiter. A positive number returns the substring to the left of the nth delimiter. A negative number returns the substring to the right of the nth delimiter from the end. This is required. |
Examples
Extracting a Substring from the Left
This example extracts the substring before the first "." in the string "www.w3schools.com".
Syntax
SELECT SUBSTRING_INDEX("www.w3schools.com", ".", 1);
Output
www
Extracting a Substring from the Left (Multiple Delimiters)
This example extracts the substring up to the second ".".
Syntax
SELECT SUBSTRING_INDEX("www.w3schools.com", ".", 2);
Output
www.w3schools