Name
FIND_IN_SET()
Synopsis
FIND_IN_SET(string
,string_list
)
This function returns the location of the first argument within a comma-separated list that is passed as a single string in the second argument. The first element of the list is 1. A 0 is returned if the string is not found in the set or if the string list is empty. It returns NULL if either argument is NULL.
As an example of how this function might be used, suppose that a
table in our college application contains the results of a survey that
students took on the college’s web site. One of the columns,
favorite_activities
, contains a list of activities
each student said is her favorite in the order that she likes them,
her favorite being first. The text of the column comes from a web form
on which students entered a number to rank each activity they like;
they left blank the ones they don’t take part in. So, each column has
text separated by commas and spaces (e.g., bike riding,
reading, swimming). Here’s how this function could be used
to order a list of students who said that reading
is one of their favorite activities:
SELECT student_id, FIND_IN_SET('reading', REPLACE(favorite_activities, SPACE(1), '') ) AS reading_rank FROM student_surveys WHERE survey_id = 127 AND favorite_activities LIKE '%reading%' ORDER BY reading_rank;
We use the WHERE
clause to choose the correct
survey and the LIKE
operator to select only rows
where the column favorite_activities
contains the
value reading. This will eliminate those students who didn’t ...
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.