MySQL SUBSTRING_INDEX() Function

 The SUBSTRING_INDEX() function in MySQL is used to extract a part of a string before or after a specified delimiter. It can return the substring from the beginning of a string up to a given delimiter or after a delimiter.

Syntax:

SUBSTRING_INDEX(string, delimiter, count)

• string: The input string from which you want to extract the substring.
• delimiter: The delimiter (character or substring) that defines where to split the string.
• count: The number of occurrences of the delimiter to consider. If the count is positive, it returns the part before the specified number of occurrences of the delimiter. If the count is negative, it returns the part after the specified number of occurrences of the delimiter.

Examples:

1. Extract substring before a delimiter (positive count):

SELECT SUBSTRING_INDEX('apple,banana,cherry', ',', 1);

This returns:

apple

It extracts the part before the first comma.

2. Extract substring after a delimiter (negative count):

SELECT SUBSTRING_INDEX('apple,banana,cherry', ',', -1);

This returns:

cherry

It extracts the part after the last comma.

3. Extract substring before the second delimiter (positive count):

SELECT SUBSTRING_INDEX('apple,banana,cherry', ',', 2);

This returns:

apple,banana

It extracts the part before the second comma.

4. Extract substring after the second delimiter (negative count):

SELECT SUBSTRING_INDEX('apple,banana,cherry', ',', -2);

This returns:

banana,cherry

It extracts the part after the second comma.

Notes:

• If the delimiter is not found in the string, the function will return the entire string.
• If count is 0, the function will return an empty string.

The SUBSTRING_INDEX() function is quite useful when dealing with strings that follow a certain format or structure (e.g., CSV data or email addresses) and you need to extract specific parts of the string.