SUBSTRING_INDEX()

USAGE

SUBSTRING_INDEX(string, delimiter, count)

DETAILS

SUBSTRING_INDEX() is a string function that returns a substring from a source string that is limited by a delimiter string.

Each occurrence of the delimiter string are found within the source string, and are used as potential split locations for returning the substring.

If the count is 1 then the returned string ends at the first occurrence of the delimiter, while 2 ends at the second occurrence, etc.

If the count is -1 then the returned string begins after the last occurrence of the delimiter, while -2 begins after the second occurrence, etc.

The delimiter matching uses a case-sensitive search.

If the delimiter is not found or not enough matches of the delimiter are found, the entire source string is returned.

A NULL is returned if any argument is NULL.

EXAMPLES

SELECT SUBSTRING_INDEX('maria-db-ok', '-', 1) AS result1,
       SUBSTRING_INDEX('maria-db-ok', '-', 2) AS result2,
       SUBSTRING_INDEX('maria-db-ok', '-', 3) AS result3,
       SUBSTRING_INDEX('maria-db-ok', '-', 4) AS result4;

+---------+----------+-------------+-------------+
| result1 | result2  | result3     | result4     |
+---------+----------+-------------+-------------+
| maria   | maria-db | maria-db-ok | maria-db-ok |
+---------+----------+-------------+-------------+

SELECT SUBSTRING_INDEX('maria-DB-ok', 'db', 1);

+-----------------------------------------+
| SUBSTRING_INDEX('maria-DB-ok', 'db', 1) |
+-----------------------------------------+
| maria-DB-ok                             |
+-----------------------------------------+

SELECT SUBSTRING_INDEX('maria<>db<>ok', '<>', -2) AS result;

+--------+
| result |
+--------+
| db<>ok |
+--------+

CHANGE HISTORY