How the FIND_IN_SET() function works in Mariadb?

The FIND_IN_SET() function is a string function that returns the position of a string in a comma-separated list of strings.

Posted on

The FIND_IN_SET() function is a string function that returns the position of a string in a comma-separated list of strings. It can be used to search for a value in a set of values, or to filter and group rows based on a set of values.

Syntax

The syntax of the FIND_IN_SET() function is as follows:

FIND_IN_SET(str, str_list)

The function takes two arguments:

  • str is the string to be searched in the list of strings.
  • str_list is the comma-separated list of strings to be searched.

The function returns an integer value that indicates the position of str in str_list. If str is not found in str_list, the function returns 0. If either str or str_list is NULL, the function returns NULL.

Examples

Example 1: Using FIND_IN_SET() function with literal values

The following example shows how to use the FIND_IN_SET() function with literal values:

SELECT FIND_IN_SET('B', 'A,B,C,D');

The function returns 2, because ‘B’ is the second value in the list.

+------------------------------+
| FIND_IN_SET('B', 'A,B,C,D')  |
+------------------------------+
|                            2 |
+------------------------------+

Example 2: Using FIND_IN_SET() function with column values

The following example shows how to use the FIND_IN_SET() function with column values from a table. Suppose we have a table called students that has the following data:

+----+-------+--------+
| id | name  | course |
+----+-------+--------+
|  1 | Alice | Math   |
|  2 | Bob   | CS     |
|  3 | Carol | CS     |
|  4 | David | Math   |
|  5 | Eve   | Art    |
+----+-------+--------+

We can use the FIND_IN_SET() function to filter the students who are taking either Math or CS courses, by using a comma-separated list of course names:

SELECT id, name, course
FROM students
WHERE FIND_IN_SET(course, 'Math,CS') > 0;

The function returns the position of each course name in the list, and the WHERE clause filters the students who have a positive position value, which means they are taking either Math or CS courses.

+----+-------+--------+
| id | name  | course |
+----+-------+--------+
|  1 | Alice | Math   |
|  2 | Bob   | CS     |
|  3 | Carol | CS     |
|  4 | David | Math   |
+----+-------+--------+

Example 3: Using FIND_IN_SET() function with expressions

The following example shows how to use the FIND_IN_SET() function with expressions. Suppose we have a table called orders that has the following data:

+----+---------+----------+------------+
| id | product | quantity | order_date |
+----+---------+----------+------------+
|  1 | A       |       10 | 2020-01-01 |
|  2 | B       |       20 | 2020-01-02 |
|  3 | C       |       30 | 2020-01-03 |
|  4 | D       |       40 | 2020-01-04 |
|  5 | E       |       50 | 2020-01-05 |
+----+---------+----------+------------+

We can use the FIND_IN_SET() function to group the orders by the month of the order date, by using the MONTH() function to extract the month from the order_date column:

SELECT SUM(quantity) AS total_quantity, FIND_IN_SET(MONTH(order_date), '1,2,3') AS quarter
FROM orders
GROUP BY quarter;

The function returns the position of the month value in the list, and the GROUP BY clause groups the orders by the quarter value.

+----------------+---------+
| total_quantity | quarter |
+----------------+---------+
|            150 |       1 |
+----------------+---------+

Some of the functions that are related to the FIND_IN_SET() function are:

  • FIELD() function: This function returns the position of a value in a list of values. It is similar to the FIND_IN_SET() function, but it does not require the list of values to be comma-separated. For example, FIELD('B', 'A', 'B', 'C', 'D') returns 2.
  • IN() operator: This operator returns 1 if a value is in a list of values, or 0 otherwise. It is similar to the FIND_IN_SET() function, but it does not return the position of the value in the list. For example, 'B' IN ('A', 'B', 'C', 'D') returns 1.
  • SUBSTRING_INDEX() function: This function returns a substring from a string before a specified number of occurrences of a delimiter. It can be used to extract a value from a comma-separated list of values. For example, SUBSTRING_INDEX('A,B,C,D', ',', 2) returns ‘A,B’.

Conclusion

The FIND_IN_SET() function is a useful string function that can be used to search for a value in a comma-separated list of values. It returns the position of the value in the list, or 0 if the value is not found. It can be used with literal values, column values, or expressions. It can also be combined with other functions or operators to perform complex queries.