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

Updated on 20-Jun-2020 11:30:25

• Related Questions & Answers
• What is the use of MySQL TRUNCATE() function?
• What is the use of MySQL UNHEX() function?
• What is the use of MySQL CHAR() function?
• What is the use of MySQL FROM_UNIXTIME() function?
• What is the use of MySQL GET_FORMAT() function?
• What is the use of MySQL LAST_INSERT_ID() function?
• What is the use of MySQL CONCAT_WS() function?
• What is the use of FIND_IN_SET () function in MySQL?
• What is the use of LOCATE() function in MySQL?
• What is the use of MySQL IFNULL() control flow function?
• What is the use of MySQL NULLIF() control flow function?
• What is the use of JavaScript eval function?
• Get the index of last substring in a given string in MySQL?
• What is the use of NCHAR in MySQL?
• What is the use of Math.imul( ) Function in JavaScript?