Table of Contents for

MySQL in a Nutshell, 2nd Edition

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 rank reading as a favorite activity from the results. FIND_IN_SET() won’t allow spaces because they confuse the function, so we need to remove spaces from the text in the favorite_activities column. Thus, we slip in a call to REPLACE() to replace any space found with an empty string. With that done, FIND_IN_SET() will return the ranking each student gave for reading. The ORDER BY clause orders those results by reading_rank—the alias given for the second field with the AS clause.