Table of Contents for

MySQL in a Nutshell, 2nd Edition

This function searches for the first string given in the following list of strings, and returns the numeric position of the first string in the list that matches. The first element is 1 among the arguments being searched. If the search string is not found or is NULL, 0 is returned.

As an example of this function, suppose that in a table containing telephone numbers of students at a college, there are three columns for telephone numbers (dormitory, home, and work numbers). Suppose further that another column is used to indicate which column contains the primary telephone number of the student. However, we realize that for many rows this primary_phone column is NULL. So, we decide to make a guess as to which is the primary telephone number by using the FIELD() function along with a subquery:

UPDATE students
JOIN
  (SELECT student_id,
   FIELD(1, phone_dorm IS TRUE,
            phone_home IS TRUE,
            phone_work IS TRUE)
   AS first_phone_found
   FROM students
   WHERE primary_phone IS NULL) AS sub_table
   USING (student_id)
SET primary_phone = first_phone_found;

Notice that in the subquery, within the FIELD() function, we’re looking for a value of 1 (the first parameter of the function). For the other parameters given, each telephone column will be examined using the IS TRUE operator: it will return true (or rather 1) if the column is not NULL. The FIELD() function will return the number of the element in the list that returns 1 (meaning it exists). So if phone_dorm is NULL but phone_home has a telephone number in it, the subquery will return a value of 2—even if phone_work also contains a number. The JOIN uses the results to update each student record that has a NULL value for primary_phone with the value of the first_phone_found field in the results of the subquery.