The SUBSTRING_INDEX() function is used to extract a substring from a string before or after a specified number of occurrences of a delimiter.
Syntax
SUBSTRING_INDEX(string, delimiter, number)
Parameters
| Parameter | Description |
|---|---|
string |
Required. The original string from which the substring is to be extracted. |
delimiter |
Required. The delimiter that separates the parts of the string. |
number |
Required. The number of times the delimiter occurs. Can be positive or negative. If positive, the function returns everything to the left of the nth occurrence of the delimiter. If negative, it returns everything to the right of the nth occurrence of the delimiter. |
Definition and Usage
- The
SUBSTRING_INDEX()function extracts a substring from the original string, either to the left or right of the specified number of delimiter occurrences. - Positive
number: Extracts the substring before the nth occurrence of the delimiter. - Negative
number: Extracts the substring after the nth occurrence of the delimiter.
Return Values
- The function returns the substring based on the given delimiter and the occurrence count.
- If the delimiter is not found, it returns the entire string.
Usage Examples
Example 1: Return a substring before the first occurrence of the period .
SELECT SUBSTRING_INDEX("www.w3schools.com", ".", 1);
Output:
| SUBSTRING_INDEX("www.w3schools.com", ".", 1) |
|---|
| www |
Explanation: The function extracts everything before the first period in the string.
Example 2: Return a substring before the second occurrence of the period .
SELECT SUBSTRING_INDEX("www.w3schools.com", ".", 2);
Output:
| SUBSTRING_INDEX("www.w3schools.com", ".", 2) |
|---|
| www.w3schools |
Explanation: The function extracts everything before the second period in the string.
Example 3: Return a substring after the second occurrence of the period .
SELECT SUBSTRING_INDEX("www.w3schools.com", ".", -2);
Output:
| SUBSTRING_INDEX("www.w3schools.com", ".", -2) |
|---|
| w3schools.com |
Explanation: The function extracts everything after the second period, starting from w3schools.com.
Example 4: Return a substring after the first occurrence of the period .
SELECT SUBSTRING_INDEX("www.w3schools.com", ".", -1);
Output:
| SUBSTRING_INDEX("www.w3schools.com", ".", -1) |
|---|
| schools.com |
Explanation: The function extracts everything after the first period.
Technical Details
- Works in: MySQL 4.0 and later.
- Return Type: String (the extracted substring).
- Delimiter: If the delimiter is not found in the string, the function returns the entire string.
Applications
- Domain Extraction: You can use
SUBSTRING_INDEX()to extract the domain name from a URL by splitting the string at the periods. - CSV Parsing: When handling data in CSV format or similar delimited structures, this function can be used to extract specific fields based on their delimiter positions.
- Text Processing: For applications that need to process structured text,
SUBSTRING_INDEX()can help split strings into useful components.
Key Notes
- The delimiter used must exist in the string for the function to operate correctly.
- Using negative numbers is useful when you need the substring after a specific delimiter occurrence.