Introduction to MySQL field()
MySQL Field() function is a case insensitive search function which is performed to get the index position of a string or number value in the list of string or number values. This Field() function is said to be a complement type function of ELT() in the server.
The function accepts both strings and numbers as arguments given by the user. When the arguments are strings of the Field() then, the comparison is done according to strings, and when the arguments provided are numbers, then the searching is done on the basis of numbers. In case it compares all as double if the type of values is default otherwise.
If a query is executed with Field() function to find the index position of a specific value and if the value is not found then, the function results in zero(0). Also, if the value to be searched is NULL then, the function with a NULL argument will fail to compare the equality with any value, and 0 becomes the return value.
Syntax
Let us view the syntax structure of the MySQL Field() function to be used in the MySQL server written below:
FIELD(String/Number Value, value1,value2,value3,…………)
Here, we will describe the terms as:
- String/Number Value: This parameter denotes the required value to be searched in the specified list and can be any string or any integer(number).
- value1, value2, value3, …………: This sequence of the required parameter is defined as the values where the first parameter will be searched by the Field() function.
Also, let us discuss the result cases as follows:
- If the function does not find the required value in the list of values (value1, value2, value3, …………), then it will return 0.
- If the parameter is NULL then, the Field() function will again output 0.
- If the function holds all the argument values as strings, then the comparison is performed as strings to return the string index position.
- If the function holds all argument values as numbers, then the searching is executed as numbers to result in the index position of the number value.
How field() function works in MySQL?
MySQL database manages and stores different types of data values and provides operations to fetch certain information about the records. This process of query execution and knowing the result helps for maintaining the database records and related tables updated and secured for retrieving any table records such as indexes, column values or total, average, max, min, positions, relation, etc. In addition, MySQL supports many built-in functions that are beneficial to make changes and query data info from the database tables.
In this way, one of the string functions can be the MySQL Field() function that helps to fetch the index position of a particular value, either number or string, from a list of numbers or string values, respectively. To show the process and working of Field() function through queries used to implement the function are explained below:
Suppose we will find the index position of a substring from a list of substrings as follows:
SELECT FIELD(‘g’, ’a’, ’m’, ’s’, ’g’, ’c’);
This query using the Field() function will output 4 as an index position. Also, we have used the SELECT keyword with the function to display the result after the search.
Also, since the Field() function performs a case insensitive search then, let us see the following example:
SELECT FIELD(‘G’, ’a’, ’m’, ’s’, ’g’, ’c’);
The output for this is also 4 as the character is upper case or lower case, so the search is not affected.
Examples of MySQL field()
Let us demonstrate some examples using MySQL Field() function to explore the uses of it specified as follows:
Example #1 – Using MySQL Field() function
Suppose we have few values in the list of strings and we are finding the index position for one of the values either present or not present in the list of values as shown below:
SELECT FIELD('g','a', 'm', 's', 'g', 'c');
Output:
SELECT FIELD('S', 'a', 'm', 's', 'g', 'c');
Output:
SELECT FIELD('MySQL', 'SQL', 'PHP', 'HTML', 'CSS', 'Python');
Output:
SELECT FIELD('1', '5', '8', '0', '3', '9')
Output:
SELECT FIELD('NULL', 'a', 'm', 's', 'g', 'c');
Output:
SELECT FIELD('v', 'NULL');
Output:
With the above examples, we can analyze that if the provided argument is found in the list of values then, the function gives the index position of it and if not found then, it returns zero(0) as output value and also if anyone of the argument in the function is NULL.
Example #2 – Using database table & ORDER BY clause
For this, we will use the succeeding syntax code provided as follows:
12 Online Courses | 10 Hands-on Projects| 92+ Hours| Verifiable Certificate of Completion
4.5
View Course
SELECT * FROM TableName [ORDER BY] FIELD(ColumnName, Value1, Value2, Value3, ……., ValueN) DESC;
Here, TableName is the sample table to be used for this query search function, and ColumnName is the first parameter which is to be searched from the list of items that contains the values from the table.
To understand the Field() function to be used in the database table, let us first query for creating the table below:
CREATE TABLE Books(BookID INT PRIMARY KEY, BookName VARCHAR(255) NOT NULL, Language VARCHAR(255) NOT NULL, Price INT NOT NULL);
Again, let us add some records using the INSERT query in the table Books created above:
INSERT INTO Books(BookID, BookName, Language, Price) VALUES(‘101’,’Algebraic Maths’,’English’,’2057’);
And so on, other book rows are also inserted.
Now, let us display the contents of the table Books with SELECT query as:
SELECT * FROM Books;
Output:
Next, we will query with MySQL Field() function in the table Books to search for a specific index position for the column from a sequence of values related to the first argument.
We can also sort the order of the values in the result set using the MySQL ORDER BY clause with the above Field() function command either in ascending order with ASC keyword or DESC keyword for descending order:
SELECT * FROM Books ORDER BY FIELD(BookID, 101,102,103,104,105,106,107,108,109) DESC;
Output:
So, we can additionally use the ORDER BY clause with Field() function to sort the values.
Conclusion
MySQL Field() function is also a type of MySQL String function that retrieves the index position of a data value as indicated with the first argument of the function by searching or matching the remaining value expressions stated in the second argument as a list of values parted with comma.
The function is useful for finding out the existing index position and results if the one is not present.
Recommended Articles
This is a guide to the MySQL field(). Here we discuss How the field() function works in MySQL and Examples along with the outputs. You may also have a look at the following articles to learn more –
