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.
Get MySQL in a Nutshell, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.