FIND_IN_SET( )

This MySQL function returns the location of the first argument within a comma-separated list that is passed as a single stringin the second argument.

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

FIND_IN_SET(string, string_list)

Explanation

This function returns the location of the first argument within a comma-separated list that is passed as a single stringin 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.

Examples

As an example of how this function might be used, suppose that in 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 that each student said is her favorite and in the order that she like them—her favorite being first. The text of the column came from a web form where students entered a number ranking each activity that they like; they left blank the ones that they don't do. 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 it. 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.