MySQL in a Nutshell, 2nd Edition

Name

FIELD()

Synopsis

FIELD(string, string[, ...])

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