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
needle
is the string that you want to find. - The second parameter
haystack
is 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
NULL
value if eitherneedle
orhaystack
is NULL. - Return zero if the
needle
is not in thehaystack
or thehaystack
is an empty string. - Return a positive integer if the
needle
is 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'); -- 2
Code 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.