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:

Syntax Diagram:

MySQL Version: 5.6

Video Presentation:

Pictorial Presentation

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.

Online Practice Editor:

All String Functions

Previous: FIELD
Next: FORMAT