What is the use of MySQL SUBSTRING_INDEX() function?

MySQL SUBSTRING_INDEX() function returns the substring from a given string before the occurrences of a delimiter. The number of occurrences is specified as an argument of it. It would be more understood with the help of syntax of SUBSTRING_INDEX() function as follows −

Syntax

SUBSTRING_INDEX(Str, delim, count)

Here, 

• Str is the string from which it returns the substring.
• Delim is the delimiter which is to be search in string.
• Count is the number of times the delimiter is to be searched.

Example

mysql> Select SUBSTRING_INDEX('My Name is Ram','a',2);
+-----------------------------------------+
| SUBSTRING_INDEX('My Name is Ram','a',2) |
+-----------------------------------------+
| My Name is R                            |
+-----------------------------------------+
1 row in set (0.00 sec)

The above query returns the substring before the occurrence of second ‘a’ because the delimiter was ‘a’ and count was 2.

mysql> Select SUBSTRING_INDEX('My Name is Ram','a',1);
+-----------------------------------------+
| SUBSTRING_INDEX('My Name is Ram','a',1) |
+-----------------------------------------+
| My N                                    |
+-----------------------------------------+
1 row in set (0.00 sec)

The above query returns the substring before the occurrence of first ‘a’ because the delimiter was ‘a’ and count was 1.

Arjun Thakur

Published on 20-Feb-2018 16:48:15