Archive for January 31st, 2012

The SQL FIELD() Function

The FIELD() function is, in a sense, the inverse of ELT() in that ELT will return a string from a specific numbered position in an array of strings, while FIELD() returns the field position that matches (not the same as “contains”) the string listed as the first parameter. Syntax:

SELECT FIELD(‘string_to_match’, ‘string1′, ‘string2′, ‘etc.’)

Here is the field function in action:

SELECT FIELD(‘Scarlet’, ‘Melanie’, ‘Rhett’, ‘Scarlet’, ‘Ashley’, ‘Scarletta’)

This will return “3”. Though 5 contains “Scarlet”, it is not equivalent to “Scarlet”. Had position 5 also been equivalent to “Scarlet” the result would still have been only “3”. Later matches are ignored.

Both FIELD() and ELT() are a kind of test. They can be used when you are looking for one item out of a group of alternatives. The result can be cranked through a series of if/then statements or switch/case to determine a course of action. This is another place where some of the work done by the server can be taken on by the database.

Read more about SQL Functions.

No Comments