SUBSTRING_INDEX( )

This MySQL function returns a substring of string, using delimiter to separate substrings and count to determine which of the substrings to return.

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

SUBSTRING_INDEX(string, delimiter, count)

Explanation

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.

Examples

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 pre-requisite courses separated by vertical bars. The statement displays the last prerequisite, because -1 was entered for the count.