MySQL: SUBSTRING_INDEX Function

This tutorial explains how to use the MySQL SUBSTRING_INDEX function with syntax and examples.

Description

The MySQL SUBSTRING_INDEX function returns the substring of string before number of occurrences of delimiter.

Syntax

The syntax for the SUBSTRING_INDEX function in MySQL is:

SUBSTRING_INDEX( string, delimiter, number )

Parameters or Arguments

string

The source string.

delimiter

The delimiter to search for in string.

number

The number of times to search for delimiter.

Note

• If number is a negative value, everything from the left of the targeted delimiter is returned by the SUBSTRING_INDEX function.
• If number is a positive value, everything from the right of the targeted delimiter is returned by the SUBSTRING_INDEX function.
• See also SUBSTRING function.

Applies To

The SUBSTRING_INDEX function can be used in the following versions of MySQL:

• MySQL 5.7, MySQL 5.6, MySQL 5.5, MySQL 5.1, MySQL 5.0, MySQL 4.1, MySQL 4.0, MySQL 3.23

Example

Let's look at some MySQL SUBSTRING_INDEX function examples and explore how to use the SUBSTRING_INDEX function in MySQL.

For example:

mysql> SELECT SUBSTRING_INDEX('www.AODBA.com', '.', 1);
Output: 'www'

mysql> SELECT SUBSTRING_INDEX('www.AODBA.com', '.', 2);
Output: 'www.AODBA'

mysql> SELECT SUBSTRING_INDEX('www.AODBA.com', '.', -1);
Output: 'com'

mysql> SELECT SUBSTRING_INDEX('www.AODBA.com', '.', -2);
Output: 'AODBA.com'