MySQL SUBSTRING_INDEX() Function

In MySQL, the SUBSTRING_INDEX() function returns the substring from a specified string before specified number of occurrences of a specified delimiter.

SUBSTRING_INDEX() Syntax

Here is the syntax of MySQL SUBSTRING_INDEX() function:

SUBSTRING_INDEX(str, delim, count)

Parameters

str
Required. A string.
delim
Required. The delimiter to search for in the string str.
count
Required. The number of occurrences ofdelim appears. It can be negative.

Return value

The SUBSTRING_INDEX() function returns the substring from a specified string before specified number of occurrences of a specified delimiter.

  • If count is 0, the SUBSTRING_INDEX() function returns an empty string '';
  • If count is negative, the delimiter is counted forward from the end of the string, and the substring after the delimiter is returned.
  • The function will return NULL if any parameter is NULL.

SUBSTRING_INDEX() Examples

Here are some examples of MySQL SUBSTRING_INDEX() function.

SELECT
    SUBSTRING_INDEX('www.sqliz.com', '.', 2),
    SUBSTRING_INDEX('www.sqliz.com', '.', -2),
    SUBSTRING_INDEX('www.sqliz.com', '.', 0)\G

 SUBSTRING_INDEX('www.sqliz.com', '.', 2): www.sqliz
SUBSTRING_INDEX('www.sqliz.com', '.', -2): sqliz.com
 SUBSTRING_INDEX('www.sqliz.com', '.', 0):