How the FIND_IN_SET() function works in Mariadb?

The MariaDB FIND_IN_SET() function is used to determine the position of a string within a list of comma-separated values. It is particularly useful for searching within sets that are stored as strings and for performing operations that involve set membership.

Syntax

The syntax for the MariaDB FIND_IN_SET() function is as follows:

FIND_IN_SET(str, strlist)

• str is the string to find.
• strlist is the string containing the list of comma-separated values.

Examples

Example 1: Finding the Position of a String

To find the position of ‘b’ in a list:

SELECT FIND_IN_SET('b', 'a,b,c,d') AS position;

The output will be:

+----------+
| position |
+----------+
|        2 |
+----------+

This indicates that ‘b’ is the second item in the list.

Example 2: String Not Found in the List

When the string is not found in the list:

SELECT FIND_IN_SET('e', 'a,b,c,d') AS position;

The output will be:

+----------+
| position |
+----------+
|        0 |
+----------+

Since ’e’ is not in the list, the function returns 0.

Example 3: Finding the Position with a Variable

Using a variable to find the position of a string:

SET @member = 'c';
SELECT FIND_IN_SET(@member, 'a,b,c,d') AS position;

The output will be:

+----------+
| position |
+----------+
|        3 |
+----------+

The variable ‘@member’ holds the value ‘c’, which is the third item in the list.

Example 4: Using FIND_IN_SET() in a WHERE Clause

To filter rows based on set membership:

SELECT * FROM table_name WHERE FIND_IN_SET('value', column_name);

This query will return rows where ‘value’ is found within the comma-separated set in ‘column_name’.

Example 5: FIND_IN_SET() with Dynamic List

Creating a dynamic list and finding the position of a string:

SET @list = CONCAT_WS(',', 'a', 'b', 'c', 'd');
SELECT FIND_IN_SET('d', @list) AS position;

The output will be:

+----------+
| position |
+----------+
|        4 |
+----------+

The CONCAT_WS() function creates a dynamic list, and ’d’ is found as the fourth item.

Related Functions

Here are a few functions related to the MariaDB FIND_IN_SET() function:

• MariaDB FIELD() function returns the index position of a string within a list of strings.
• MariaDB ELT() function returns the N-th element from a list of strings.
• MariaDB CONCAT_WS() function concatenates strings with a specified separator.

Conclusion

The FIND_IN_SET() function in MariaDB is an essential tool for working with sets represented as comma-separated strings. It allows for efficient searching and can be used in various scenarios, such as filtering data based on set membership or ordering results by set position. Understanding how to use FIND_IN_SET() can greatly enhance the manipulation and analysis of set-based data in MariaDB.