MySQL Tutorial MySQL Advanced MySQL Database Account Management MySQL References

MySQL FIND_IN_SET() Function



The MySQL FIND_IN_SET() function returns the position of a string in a comma-delimited string list. In the special case it returns the following:

  • If string is not found in string_list, the function returns 0.
  • If string is NULL, the function returns NULL.
  • If string_list is an empty string, the function returns 0.
  • If string_list is NULL, the function returns NULL.

Syntax

FIND_IN_SET(string, string_list)

Parameters

string Required. Specify the string to find.
string_list Required. Specify the list of string values separated by commas that need to be searched.

Return Value

Returns the position of string in string_list.

Example 1:

The example below shows the usage of FIND_IN_SET() function.

mysql> SELECT FIND_IN_SET('b', 'a,b,c,d');
Result: 2

mysql> SELECT FIND_IN_SET('b', 'A,B,C,D');
Result: 2

mysql> SELECT FIND_IN_SET('B', 'a,b,c,d');
Result: 2

mysql> SELECT FIND_IN_SET(3, '1,2,3,4');
Result: 3

mysql> SELECT FIND_IN_SET('z', 'a,b,c,d');
Result: 0

mysql> SELECT FIND_IN_SET('a', '');
Result: 0

mysql> SELECT FIND_IN_SET('a', NULL);
Result: NULL

mysql> SELECT FIND_IN_SET(NULL, 'a,b,c,d');
Result: NULL

Example 2:

Consider a database table called Employee with the following records:

EmpIDNameDepartments
1JohnHR,Admin
2MarryIT,Finance
3JoFinance,Marketing
4Kim'Marketing,Sales
5RameshIT,Finance,Marketing
6HuangIT,Marketing,Sales

The query given below can be used to find out the employee names of 'Finance' department.

SELECT *, IF(FIND_IN_SET('Finance', Departments), 'YES', 'NO') AS Result 
FROM Employee;

This will produce the result as shown below:

EmpIDNameDepartmentsResult
1JohnHR,AdminNO
2MarryIT,FinanceYES
3JoFinance,MarketingYES
4KimMarketing,SalesNO
5RameshIT,Finance,MarketingYES
6HuangIT,Marketing,SalesNO

❮ MySQL Functions

5