MySQL FIND_IN_SET Function
Summary: in this tutorial, you will learn how to use the MySQL FIND_IN_SET() function to return the position of a string in a comma-separated list of strings.
Introduction to MySQL FIND_IN_SET() function
MySQL provides a built-in string function called FIND_IN_SET() that allows you to find the position of a string within a comma-separated list of strings.
The following illustrates the syntax of the FIND_IN_SET function:
FIND_IN_SET(needle,haystack);Code language: SQL (Structured Query Language) (sql)
The FIND_IN_SET function accepts two parameters:
- The first parameter
needleis the string that you want to find. - The second parameter
haystackis a list of comma-separated strings that to be searched.
The FIND_IN_SET() function returns an integer or a NULL value depending on the value of the arguments:
- Return a
NULLvalue if eitherneedleorhaystackis NULL. - Return zero if the
needleis not in thehaystackor thehaystackis an empty string. - Return a positive integer if the
needleis in thehaystack.
Note that the function will not work properly if the needle contains a comma (,). In addition, MySQL will use bit arithmetic optimization if the needle is a constant string and the haystack is a column of type SET.
The MySQL FIND_IN_SET() examples
Let’s take some examples to understand how the MySQL FIND_IN_SET works.
A) MySQL FIND_IN_SET() function simple examples
The following statement returns 2 because y has the second position in 'x,y,z' string.
SELECT FIND_IN_SET('y','x,y,z'); -- 2Code language: SQL (Structured Query Language) (sql)
The following statement returns 0 because a is not in the 'x,y,z' list.
SELECT FIND_IN_SET('a','x,y,z');Code language: SQL (Structured Query Language) (sql)
The following statement also returns 0 because the second argument is empty.
SELECT FIND_IN_SET('a','');Code language: SQL (Structured Query Language) (sql)
The following statement returns NULL because the first argument is NULL.
SELECT FIND_IN_SET(NULL,'x,y,z');Code language: SQL (Structured Query Language) (sql)
The following statement also returns NULL because the second argument is NULL.
SELECT FIND_IN_SET('a',NULL);Code language: SQL (Structured Query Language) (sql)
B) Using MySQL FIND_IN_SET() function to query data from a table example
First, create a new table named divisions using the following statement.
CREATE TABLE IF NOT EXISTS divisions (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(25) NOT NULL,
belts VARCHAR(200) NOT NULL
);Code language: SQL (Structured Query Language) (sql)
Second, insert some rows into the divisions table.
INSERT INTO divisions(name,belts)
VALUES ('O-1','white,yellow,orange'),
('O-2','purple,green,blue'),
('O-3','brown,red,black'),
('O-4','white,yellow,orange'),
('O-5','purple,green,blue'),
('O-6','brown,red'),
('O-7','black'),
('O-8','white,yellow,orange'),
('O-9','purple,green,blue'),
('O-10','brown,red');
Code language: SQL (Structured Query Language) (sql)
Third, to find the division that accepts the red belt, you can use the FIND_IN_SET() function as shown in the following query:
SELECT
name,
belts
FROM
divisions
WHERE
FIND_IN_SET('red', belts);Code language: SQL (Structured Query Language) (sql)
Negating FIND_IN_SET() function
Because the FIND_IN_SET() function returns zero, which is FALSE in MySQL, when the first argument is not found in the second argument. Therefore, you can use the NOT operator to negate the FIND_IN_SET() function.
This example uses the NOT operator with the FIND_IN_SET() function to find the divisions that do not accept the black belt:
SELECT
name, belts
FROM
divisions
WHERE
NOT FIND_IN_SET('black', belts);Code language: SQL (Structured Query Language) (sql)
MySQL FIND_IN_SET() function vs. IN operator
The IN operator determines whether a value matches any value in a set. The following example uses the IN operator to find the division whose name is O-1 or O-2:
SELECT
name, belts
FROM
divisions
WHERE
name IN ('O-1' , 'O-2');Code language: SQL (Structured Query Language) (sql)
This statement uses the FIND_IN_SET() function and returns the same result as the above query:
SELECT
name,
belts
FROM
divisions
WHERE
FIND_IN_SET(name, 'O-1,O-2');Code language: SQL (Structured Query Language) (sql)
So the column IN (x, y, z) expression is the same as FIND_IN_SET(column, 'x,y,z').
The IN operator can take any number of arguments, each separated by a comma while the FIND_IN_SET() function can take only two arguments.
You use the IN operator when you want to match a value with any value a list. And you use the FIND_IN_SET() function when you want to match a value with a comma-separated list of values.
In this tutorial, you have learned how to use the MySQL FIND_IN_SET() function to find a string in a comma-separated list of strings.