How to Use the MySQL FIND_IN_SET() Function
In this tutorial, you'll learn how to use the MySQL FIND_IN_SET() function. FIND_IN_SET returns the position of a string within a list of strings.
MySQL FIND_IN_SET() Syntax
The syntax of the FIND_IN_SET function is as follows:
FIND_IN_SET(string, string_list)
Arguments
- String: Required. The string to search for.
- String_list: Required. The list of strings to be searched.
Remarks
- The function returns 0 if string is not found in string_list, or string_list is empty string ("").
- The function returns NULL if either argument is NULL.
- FIND_IN_SET is not case-sensitive.
FIND_IN_SET() Examples
Example 1
The following statement searches for Grape within a list of strings:
SELECT FIND_IN_SET("Grape", "Apple,Orange,Grape,Grapefruit,Melon,Watermelon");
Result:
3
Grape is found in the list, located at the 3rd position.
Example 2
The following statement searches for Mango within a list of strings:
SELECT FIND_IN_SET("Mango", "Apple,Orange,Grape,Grapefruit,Melon,Watermelon");
Result:
0.
Since the function returns 0, Mango is not found.
Example 3
The following statement searches for Orange in a list of strings:
SELECT FIND_IN_SET("Orange", "Apple,Orange ,Grape,Grapefruit,Melon,Watermelon");
Result:
0
Orange is not found in the list, because "Orange " contains a trailing space. Make sure that each string in a list doesn't contain leading or trailing spaces.
Example 4
The following statement searches for I want to learn how to design MySQL database in a list of strings:
SELECT FIND_IN_SET("I want to learn how to design MySQL database", NULL);
Result:
NULL.
Because the second argument is NULL.
Example 5
The following statement searches for ORANGE in a list of strings:
SELECT FIND_IN_SET("ORANGE", "Apple,Orange,Grape,Grapefruit,Melon,Watermelon");
Result:
2
Since FIND_IN_SET is not case-sensitive, ORANGE and Orange are treated the same.
In this tutorial, you've learned how to use the MySQL FIND_IN_SET function. FIND_IN_SET returns the position of a string within a list of strings. If the string that you search for not found, or a list of strings is empty, it returns 0. And it returns NULL if either string or a list of strings is NULL.