w3resource
MYSQL Tutorial

MySQL FIND_IN_SET() function

FIND_IN_SET() function

MySQL FIND_IN_SET() returns the position of a string if it is present (as a substring) within a list of strings. The string list itself is a string contains substrings separated by ‘,’ (comma) character.

This function returns 0 when search string does not exist in the string list and returns NULL if either of the arguments is NULL.

Syntax:

FIND_IN_SET (search string, string list)

Arguments

Name Description
search string A string which is to be looked for in following a list of arguments.
string list List of strings to be searched if they contain the search string.

Syntax Diagram:

MySQL FIND_IN_SET() Function - Syntax Diagram

MySQL Version: 5.6

Video Presentation

Pictorial Presentation

MySQL FIND_IN_SET function

Example: FIND_IN_SET() function

The following MySQL statement finds the search string ‘ank’ at the 2nd place within the string list. So it returns 2.

Code:

SELECT FIND_IN_SET('ank','b,ank,of,monk');

Sample Output:

mysql> SELECT FIND_IN_SET('ank','b,ank,of,monk'); 
+------------------------------------+
| FIND_IN_SET('ank','b,ank,of,monk') |
+------------------------------------+
|                                  2 | 
+------------------------------------+
1 row in set (0.00 sec)

Example : MySQL FIND_IN_SET with WHERE CLAUSE

The following MySQL statement find the search string as defined first 7 characters from aut_name column from the table author within the given string as specified in the argument and retrieves all columns from the concern rows.

Code:

SELECT * 
FROM author 
WHERE FIND_IN_SET(left(aut_name,7),'William,steven,jung,angalo')>0;

Sample table: author


Sample Output:

+--------+-----------------+---------+-----------+
| aut_id | aut_name        | country | home_city |
+--------+-----------------+---------+-----------+
| AUT001 | William Norton  | UK      | Cambridge | 
| AUT002 | William Maugham | Canada  | Toronto   | 
| AUT003 | William Anthony | UK      | Leeds     | 
+--------+-----------------+---------+-----------+
3 rows in set (0.13 sec)

Difference between LOCATE() and FIND_IN_SET() Function

When using LOCATE() function for integers, suppose we need 1 to return from LOCATE() if integer 3 is in the set '1,2,3,4,5,..' the following MySQL commands can be written :

Code:

SELECT IF(LOCATE(3,'1,2,3,4,5,6,7,8,9')>0,1,0);

Sample Output:

mysql> SELECT IF(LOCATE(3,'1,2,3,4,5,6,7,8,9')>0,1,0);
+-----------------------------------------+
| IF(LOCATE(3,'1,2,3,4,5,6,7,8,9')>0,1,0) |
+-----------------------------------------+
|                                       1 | 
+-----------------------------------------+
1 row in set (0.06 sec)

The above command working rightly because the set contains the number 3 , but if we write the following commands, look what happened -

Code:

SELECT IF(LOCATE(3,'11,12,13,14,15')>0,1,0);

Sample Output:

mysql> SELECT IF(LOCATE(3,'11,12,13,14,15')>0,1,0);
+--------------------------------------+
| IF(LOCATE(3,'11,12,13,14,15')>0,1,0) |
+--------------------------------------+
|                                    1 | 
+--------------------------------------+
1 row in set (0.02 sec)

Here we see from above example that, the 3 not present as a number three(3) in the given set, though the LOCATE() returns 1, because LOCATE() treate the given set as a string but not a comma seperated value, and the 3 present in the number 13.

To avoid this type of situation you can use the FIND_IN_SET() function. Here is the example below -

Code:

SELECT IF(FIND_IN_SET(3,'11,12,13,4,5,6,7,8,9')>0,1,0);

Sample Output:

mysql> SELECT IF(FIND_IN_SET(3,'11,12,13,4,5,6,7,8,9')>0,1,0);
+-------------------------------------------------+
| IF(FIND_IN_SET(3,'11,12,13,4,5,6,7,8,9')>0,1,0) |
+-------------------------------------------------+
|                                               0 | 
+-------------------------------------------------+
1 row in set (0.05 sec)

So, LOCATE() function is very much suitable for string but not as much suitable for integer.

All String Functions

MySQL String Functions, slide presentation

Many of our valued users post comments along with piece of code. Disqus may, false positively, consider those piece of code as Bad or Starnge syntax and send those comments to spam automatically. Even if that happens, we will make sure those useful comments are taken out of spam and approved. It may take a couple of days for that though, but any useful comment will be brought to public view for sure. We regret if that happend to any user. You may write us directly regarding this to - w3resource[at]yahoo[dot]com.