MySQL in a Nutshell, 2nd Edition

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 ...