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.


FIND_IN_SET (search string, string list)


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.


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)


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.


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 :


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 -


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 -


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.

Online Practice Editor:

All String Functions

MySQL String Functions, slide presentation

Previous: FIELD

Follow us on Facebook and Twitter for latest update.