MySQL FIND_IN_SET Function

Created with Sketch.

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 either needle or haystack is NULL.
  • Return zero if the needle is not in the haystack or the haystack is an empty string.
  • Return a positive integer if the needle is in the haystack.

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.

Leave a Reply

Your email address will not be published. Required fields are marked *