totn MySQL Functions

MySQL: SUBSTRING_INDEX Function

This MySQL 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 positive value, everything from the left of the targeted delimiter is returned by the SUBSTRING_INDEX function.
  • If number is a negative 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.techonthenet.com', '.', 1);
Result: 'www'

mysql> SELECT SUBSTRING_INDEX('www.techonthenet.com', '.', 2);
Result: 'www.techonthenet'

mysql> SELECT SUBSTRING_INDEX('www.techonthenet.com', '.', -1);
Result: 'com'

mysql> SELECT SUBSTRING_INDEX('www.techonthenet.com', '.', -2);
Result: 'techonthenet.com'