EDUCBA

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

MySQL FIND_IN_SET()

MySQL FIND_IN_SET()

Introduction to MySQL FIND_IN_SET()

MySQL FIND_IN_SET() function is a built-in MySQL string function that is 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 of the first value with the second one containing a list of values as substrings separated with a comma character.

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

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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);

Let us describe the two given parameters that are accepted by the FIND_IN_SET() function:

  • The initial parameter named string1 defines the string which you need to find.
  • The next parameter named stringlist denotes the list of strings differentiated by a comma that is to be examined.

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 of the parameters of the function, i.e. string1 or stringlist, has a NULL value then, the function results in value is NULL.
  • If the parameter stringlist holds an empty string or the string1 parameter is not present in the stringlist then, the function returns zero as the output value.
  • If the string1 parameter is available in the stringlist then, the function returns a positive integer value.

But note that if the string1 consists of a comma(,), then the FIND_IN_SET() function does not perform properly on execution. Further, if the string1 parameter is a constant string and the other parameter stringlist denotes a type SET column, then the MySQL server implements bit arithmetic optimization.

How FIND_IN_SET() function works in MySQL?

MySQL consists of many databases, and databases are comprised of different tables. These tables hold data in the form of different MySQL supported data types where most commonly integer and strings are mostly used.

When a MySQL user wants to find out if a specific string exits in any of certain sequences of strings divided by 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 upon 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”);

Here, 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, the function on execution will output a positive integer as 2 because the first value of the FIND_IN_SET() function is found in the second index of the list of values in the second parameter of a function, i.e. ‘g,h,k,l’.

Similarly, if we take the below query then, the function returns 0 as 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 from the database tables.

On the other side, the MySQL IN operator takes any number of arguments to show if a value is matched with 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 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 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:

Taking the previous table into account, we will show the result value of the function as empty when MySQL returns false if the substring is not found in the list values as the second argument. Thus, here to negate the MySQL function FIND_IN_SET(), we will apply the MySQL NOT operator. 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 view that there is no output 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 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 used 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

This is a guide to MySQL FIND_IN_SET(). Here we discuss How FIND_IN_SET() function works in MySQL and Examples along with the outputs. You may also have a look at the following articles to learn more –

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