MySQL FIND_IN_SET | String Position in List of Strings

MySQL FIND_IN_SET is an inbuilt function in MySQL. It returns the position of a string in a list of string separated by commas. Also, the function performs the search in a case-insensitive manner. In this article, we will discuss the MySQL FIND_IN_SET Function. Also, we will discuss a few examples of using it.

Note: The positions start from 1 and not 0.

Syntax

FIND_IN_SET(string, string_list)

Parameters

The MySQL FIND_IN_SET Function expects two parameters. The description of the parameters is as follows:

• string: The first parameter specifies the string to search in the list of strings.
• string_list: The second parameter specifies the list of strings separated by commas to search in.

Return Value

The MySQL FIELD_IN_SET Function returns the position of the string value in the list of strings. However, if the string does not exist in the list, the function returns 0. Also, if the list of strings is NULL, the function returns NULL.

Examples

Let’s discuss a few examples of using the function.

Example 1: Simple Search

For instance, consider a simple example search string.

mysql> SELECT FIND_IN_SET("a", "a,b,c") as position;
+----------+
| position |
+----------+
|        1 |
+----------+

The function returns the index position of “a” in the set “a,b,c”. It returns 1 as the string is present in the first position.

Example 2: String Not Found

For instance, consider a scenario when the string is not found in the list of strings.

mysql> SELECT FIND_IN_SET("a", "x,y,z") as position;
+----------+
| position |
+----------+
|        0 |
+----------+

The MySQL FIND_IN_SET function returns 0 if the input string does not exist in the list of strings.

Example 3: List of Strings is Empty

Also, if the list of strings is empty, the function returns 0.

mysql> SELECT FIND_IN_SET("a", "") as position;
+----------+
| position |
+----------+
|        0 |
+----------+

Example 4: List of Strings is NULL

mysql> SELECT FIND_IN_SET("a", null) as position;
+----------+
| position |
+----------+
|     NULL |
+----------+

The function returns NULL if the list of string values is NULL.

Example 4: Case-Insensitive Search

Also, the function performs a case-insensitive search. Consider the following example:

mysql> SELECT FIND_IN_SET("ABC", "a,abc,XYZ") as position;
+----------+
| position |
+----------+
|        2 |
+----------+

Conclusion

In conclusion, we discussed the MySQL FIND_IN_SET Function in this article. It returns the position of a string in a list of strings separated by commas. You can read more about it on the Official MySQL Documentation. Additionally, you can read about more MySQL String Functions on Concatly.