Imagine we have a string containing a set of some indexes or other values separated by a delimiter. This can be the result of the PHP function implode or the MySQL function GROUP_CONCAT, which join array values into a string using delimiters. And we need to use MySQL to find (or fail to find) a certain element in this array.
For this we'll use FIND_IN_SET. This function looks for an occurrence of a given string in an array that is written into another string separated by a delimiter (a comma). Unfortunately, the delimiter can't be changed - it's a comma only.
So, let's give an example.
SELECT FIND_IN_SET('3', mytable.myset) FROM mytable
This query will output the index of the found value '3' in each myset row of the mytable table.
Let's look at the source table and the query result:
mytable
---
one 93,16,24
two 12,3,100
thr 3,9,12
result
---
0
2
1
Another example:
SELECT title FROM mytable WHERE FIND_IN_SET('3', mytable.myset)
This example will output all rows of the mytable table whose myset value contains "3" (but not 33, not 63, and not 39).
Here's an example of the source table and the result of this query:
mytable
---
title myset
one 93,16,24
two 12,3,100
thr 3,9,12
result
---
two
thr
Comments