MySQL in a Nutshell, 2nd Edition

Name

SUBSTRING_INDEX()

Synopsis

SUBSTRING_INDEX(string, delimiter, count)

This function returns a substring of string, using delimiter to separate substrings and count to determine which of the substrings to return. Thus, a count of 1 returns the first substring, 2 returns the second, and so on. A negative number instructs the function to count from the right end. Here is an example:

SELECT SUBSTRING_INDEX(pre_req, '|', -1)
AS 'Last Prerequisite',
pre_req AS 'All Prerequisites'
FROM courses WHERE course_id = '1245';

+--------------------+----------------------------+
| Last Prerequisite  | All Prerequisites          |
+--------------------+----------------------------+
| ENGL-202           | ENGL-101|ENGL-201|ENGL-202 |
+--------------------+----------------------------+

In this example, the pre_req column for each course contains prerequisite courses separated by vertical bars. The statement displays the last prerequisite, because –1 was entered for the count.