How to Use the MySQL SUBSTRING_INDEX() Function
In this tutorial, you’ll learn how to use the MySQL SUBSTRING_INDEX() function.
SUBSTRING_INDEX() returns a substring of a given string before a specified number of occurrences of delimiter.
MySQL SUBSTRING_INDEX() Syntax
The syntax of the SUBSTRING_INDEX() function is as follows:
SUBSTRING_INDEX(string, delimiter, count)
Arguments
- String: Required. The text from which you want to extract a substring.
- Delimiter: Required. The delimiter to search.
- Count: Required. The number of occurrences of the delimiter. It can be a positive or negative number. If it is a positive number, this function returns all characters to the left of the delimiter. If it is a negative number, this function returns all the characters to the right of the delimiter.
Remarks
- SUBSTRING_INDEX() performs a case-sensitive match when searching for a delimiter.
SUBSTRING_INDEX() Examples
Return all characters from the left of a given string
The following statement returns all characters before the 2nd occurrences of the delimiter (.) from the left of the string:
SELECT SUBSTRING_INDEX("sub.mysql.tutorialace.com", ".", 2);
Returned value:
sub.mysql
Return all characters from the tight of a given string
The following statement returns all characters before the 2nd occurrences of the delimiter (.) from the right of the string:
SELECT SUBSTRING_INDEX("sub.mysql.tutorialace.com", ".", -2);
Returned value:
tutorialace.com
Return all characters from the left of a given string
The following statement returns all characters before the 2nd occurrences of the delimiter (|) from the left of the string:
SELECT SUBSTRING_INDEX("This is the first example.|This is the second example.|This is the third example.", "|", 2);
Returned value:
This is the first example.|This is the second example.
Return all characters from the left of a given string
The following statement returns all characters before the 1st occurrence of the delimiter (|ABCD|) from the left of the string:
SELECT SUBSTRING_INDEX("This tutorial teaches you how to use the MAX function.|ABCD|This tutorial teaches you how to use the MIN function.|ABCD|This tutorial teaches you how to use the AVG function.", "|ABCD|", 1);
Returned value:
This tutorial teaches you how to use the MAX function.
Case-sensitive match
The following statement returns all characters before the 1st occurrence of the delimiter (|ABCD|) from the left of the string:
SELECT SUBSTRING_INDEX("This tutorial teaches you how to use the MAX function.|abcd|This tutorial teaches you how to use the MIN function.|ABCD|This tutorial teaches you how to use the AVG function.", "|ABCD|", 1);
Returned value:
This tutorial teaches you how to use the MAX function.|abcd|This tutorial teaches you how to use the MIN function.
SUBSTRING_INDEX() treats |ABCD| and |abcd| differently because it performs a case-sensitive match.
In this tutorial, you’ve learned how to use the MySQL SUBSTRING_INDEX() function.
SUBSTRING_INDEX() returns a substring of a given string before a specified number of occurrences of delimiter.