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 ...
Get MySQL in a Nutshell, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.