MySQL SUBSTRING_INDEX Function with Examples


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.

Leave a Reply

Your email address will not be published. Required fields are marked *