EDUCBA

Home Data Science Data Science Tutorials MySQL Tutorial MySQL FIND_IN_SET()

MySQL FIND_IN_SET()

Updated June 3, 2023

MySQL FIND_IN_SET()

Introduction to MySQL FIND_IN_SET()

MySQL FIND_IN_SET() function is a built-in MySQL string function responsible for discovering the position of a given specific string provided in a list of strings separated by a comma. The FIND_IN_SET() function accepts two arguments that allow matching the first value with the second one containing a list of values as substrings separated by a comma character.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Generally, the FIND_IN_SET() function applies to any field in the database table with a sequence of values differentiated by a comma. The user wants to compare those values with a specific single value. It thus returns the index of the matched string within the list.

Syntax

Following is the syntax structure that illustrates the use of the FIND_IN_SET() function in the MySQL server:

FIND_IN_SET(string1, stringlist);

The FIND_IN_SET() function accepts two parameters, which are:

  • The initial parameter named string1 defines the string which you need to find.
  • The next parameter, “stringlist,” represents the list of strings that must be examined, and commas separate these strings.

According to the value of the function arguments, the MySQL FIND_IN_SET() will return the value as an integer or a NULL:

  • If either function’s parameters, i.e., string1 or stringlist, have a NULL value, the function results in a NULL value.
  • The function will return zero if the stringlist is empty or if the string1 parameter is not found in the stringlist.
  • The function returns a positive integer value if the string1 parameter is available in the stringlist.

But note that if the string1 consists of a comma(,), the FIND_IN_SET() function performs poorly on execution. If the string1 parameter is a constant string and the stringlist parameter represents a SET column type, the MySQL server will optimize using bit arithmetic.

How does the FIND_IN_SET() function works in MySQL?

MySQL consists of many databases, and each database comprises different tables. Tables in MySQL store data in various data types supported by MySQL, and the most commonly used types are integers and strings.

When a MySQL user wants to find out if a specific string exits in any of certain sequences of strings divided by a comma(,) symbol aimed for any query execution, then the built-in MySQL string function FIND_IN_SET() can be applied.

This function provides the required value depending on the search results. For example, suppose we are illustrating the following query to show how the function works in MySQL:

We will search a substring h within a list of strings using the statement below,

SELECT FIND_IN_SET("h", "g,h,k,l");

We use the SELECT statement with the FIND_IN_SET() function to evaluate and display the return value. The result from the above query is true as the first parameter, ‘h’ is present in the list as the second parameter. So, Upon execution, the function will produce a positive integer, specifically 2. This is because the first value of the FIND_IN_SET() function is found in the second index of the list of values provided in the function’s second parameter, which is ‘g,h,k,l’.

Similarly, if we take the below query, then the function returns 0 as the output value as the value is not in the list:

SELECT FIND_IN_SET("b", "g,h,k,l");

Also, when we define the query as follows then, the output is NULL as the second parameter is NULL:

SELECT FIND_IN_SET("h", NULL);

Thus, we can define the position of a string within a particular list of substrings provided by the database tables.

Conversely, the MySQL IN operator takes any number of arguments to show if a value matches any value in a set.

Examples of MySQL FIND_IN_SET()

Let us demonstrate some examples using the MySQL FIND_IN_SET() as follows:

Example #1

Example to fetch data from a table by MySQL FIND_IN_SET() function:

Suppose we have a table named collection created in our database using the query below:

CREATE TABLE IF NOT EXISTS Collection (ColID INT AUTO_INCREMENT PRIMARY KEY, ColName VARCHAR(255) NOT NULL, Subjects VARCHAR(255) NOT NULL);

Also, let us enter a few record rows into the Collection table created:

INSERT INTO Collection (ColName, Subjects) VALUES('o-1','Computers, Maths, Science'),('o-2','Networks, Maths, MySQL'),('o-3',' Computers, English, Data Science'),('o-4','Electric, Maths, Science'),('o-5','Computers, MySQL, English'),('o-6','Science, Web Design'),('o-7','Maths, Science'),('o-8','MySQL, Web Design'),('o-9','Computers');

Displaying the contents of the table as follows:

SELECT * FROM Collection;

Output:

MySQL FIND_IN_SET() output 1

Now, we will find the collection that will accept the Maths subject using the MySQL function FIND_IN_SET() shown below:

SELECT ColName, Subjects FROM Collection WHERE FIND_IN_SET('Computers', Subjects);

Output:

MySQL FIND_IN_SET() output 2

As you can see in the above output, the query has searched for the string Maths in the list of values from the table column Subjects.
Looking for a simple example and its output as follows:

SELECT FIND_IN_SET('h', 'g,h,k,l');

Output:

MySQL FIND_IN_SET() output 3

The FIND_IN_SET() function provides the position of the first argument ‘h’ as found in the sequence of values as the second argument of the function.

Example #2

Example showing Negativity of MySQL FIND_IN_SET() function:

Considering the previous table, the result value of the function will be empty when MySQL returns false. This occurs when the substring specified in the first argument is not found in the list of values provided as the second argument. Thus, we will apply the MySQL NOT operator to negate the MySQL function FIND_IN_SET(). Finally, we will illustrate the query example with FIND_IN_SET() function using the NOT operator also to search the collection that does not match the PHP subject in the table values:

SELECT ColName, Subjects FROM Collection WHERE FIND_IN_SET('PHP', Subjects);

Output:

output 4

As you can see, no output is produced as a collection because, in the list of values from column Subjects, the FIND_IN_SET() function has not found any matched substring as given in the first argument.

Example #3

Difference between IN operator and FIND_IN_SET():

The IN operator defines whether a substring matches any substring set or list and can accept any number of arguments parted by a comma as follows:

SELECT ColName, Subjects FROM Collection WHERE ColName IN ('o-1', 'o-2', 'o-5', 'o-6');

Output:

output 5

Similarly, using the FIND_IN_SET() will result in the identical output as IN query but takes only two parameters to show a match of value with a list of values divided by a comma:

SELECT ColName, Subjects FROM Collection WHERE FIND_IN_SET(ColName, 'o-1,o-2,o-5,o-6');

Output:

output 6

Conclusion

MySQL FIND_IN_SET() function allows a server to check if a substring as the first argument is present in the list of values composed of substrings in the second argument parted by a comma.

This function, when the value is searched, returns the results based on those values as a positive integer as position(if the value exists in the list), zero(if value not found) or NULL(if any argument is NULL), which can be helpful for MySQL operations at the admin level.

Recommended Articles

We hope that this EDUCBA information on “MySQL FIND_IN_SET()” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. MySQL List User
  2. BIT in MySQL
  3. MySQL CURDATE
  4. MySQL SHOW