MySQL FIND_IN_SET() function

In this context, we will learn how we can use the MySQL MOD() function with proper syntax and good examples.

Introduction of MySQL MOD() 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.

FIND_IN_SET() function is used to find the position of a string within a list of strings. If the string is repeated multiple times, the output will be the first position of that string.

Following are the Some important Points of MySQL FIND IN SET function:

1. If the string is not found in string_list, the result is 0.

2. If string or string_list is NULL, the result is NULL.

3. If string_list is an empty string (""), the result is 0.

Syntax of the MySQL MOD() function

The syntax of the MySQL MOD() function is given as follows:

FIND_IN_SET("string", "string_list")

Parameters or arguments used in MySQL MOD() function:

There are two parameters accepted by the MOD() function in MySQL, which are given as follows:

The first parameter string is the string that you want to find.The second parameter, string_list, is a list of comma-separated strings that are 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 string or string_list is NULL.
• Return zero if the string is not in the string_list or the string_list is an empty string.
• Return a positive integer if the string is in the string_list.

Note: Parameter string is mandatory to search for string_list; string_list is a list of string values.

Application used for MOD() function:

The MOD() function can be used in the given below MySQL versions.:

• MySQL 5.7
• MySQL 5.6
• MySQL 5.5
• MySQL 5.1
• MySQL 5.0
• MySQL 4.1
• MySQL 4.0
• MySQL 3.23

Examples of MySQL MOD() function:

Now we will look into some MySQL MOD() function examples and will explore how we can use the MOD function in MySQL.

Example-1:

In this example, we will search for "b" within the list of strings:

SELECT FIND_IN_SET("b," "j, a, v, a, t, p, o, i, n, t, s");

Output:

FIND_IN_SET("b," "javatpoints")
0

Example-2:

In this example, we will search for "q" within the list of strings (string list is NULL):

SELECT FIND_IN_SET("a", null);

Output: –

FIND_IN_SET(“a,” null)
null

Example-3:

In this example, we will search for "q" within the list of strings:

SELECT FIND_IN_SET("g", "g, e, e, k, s, f, o, r, g, e, e, k, s");

Output: –

FIND_IN_SET(“g”, “g, e, e, k, s, f, o, r, g, e, e, k, s”)
1

Some Simple Examples of Find_In_Set function:

Example A: The following statement returns 2 because y has the second position in the 'x,y,z' string:

SELECT FIND_IN_SET('y','x,y,z');

Output:

2

Example B: The following statement returns 0 because a is not in the 'x,y,z' list:

SELECT FIND_IN_SET('a','x,y,z');

Output:

0

Example C: The following statement also returns 0 because the second argument is empty:

SELECT FIND_IN_SET('a','');

Output:

0

Example D: The following statement returns NULL because the first argument is NULL:

SELECT FIND_IN_SET(NULL,'x,y,z');

Output:

NULL

Example E: The following statement also returns NULL because the second argument is NULL:

SELECT FIND_IN_SET('a',NULL);

Output:

NULL

Example of Find_In_Set Function with Table

First, we will make a new table named segment using the following statement.

CREATE TABLE IF NOT EXISTS segment (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(25) NOT NULL,
belts VARCHAR(200) NOT NULL
);

After that, we will add some rows to the segment table.

INSERT INTO segment(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');

Then, we can use the FIND_IN_SET() function to find the segment that accepts the red belt, as shown in the following query:

SELECT
name,
belts
FROM
segment
WHERE
FIND_IN_SET('red', belts);

Output:

Name	Belts
0-3	Red, brown, black
0-6	Red, brown
0-10	Red, brown

FIND_IN_SET() function with NOT operator

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 segment that does not accept the black belt:

SELECT
name, belts
FROM
segment
WHERE
NOT FIND_IN_SET('black', belts);

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 segment whose name is O-1 or O-2:

SELECT
name, belts
FROM
segment
WHERE
name IN ('O-1' , 'O-2');

This statement uses the FIND_IN_SET() function and returns the same result as the above query:

SELECT
name,
belts
FROM
segment
WHERE
FIND_IN_SET(name, 'O-1,O-2');

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 in a list. And you can use the FIND_IN_SET() function when you want to match a value with the specified list of values.

Summary:

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.