SELECT FIND_IN_SET('search_string','set1,set2,set3')
Here search_string is used to search on on set of strings separated by coma. SELECT FIND_IN_SET('ry', 'kpy,tyu,ryk,pkr')
No matching record so Output 0
SELECT FIND_IN_SET('ry', 'kpy,tyu,ryk,pkr,bry,ry,lkiu')
Output 6
Use of student table for getting matching record
SELECT * FROM student WHERE FIND_IN_SET('john',name) > 0
No records found
SELECT * FROM student WHERE FIND_IN_SET('Alex John',name) > 0
Outptu is here ( one record found )
6 Alex John Four 58 male
SELECT FIND_IN_SET('ry', 'kpy,tyu,ryk,pkr')
No matching record so Output is 0
SELECT LOCATE('ry', 'kpy,tyu,ryk,pkr')
Output is 9 SELECT * FROM student WHERE FIND_IN_SET(2,id) > 0
One matching record we will get, Output is here
2 Max Ruin Three 85 male
Here we get one record with id = 2 , now let us try LOCATE query using student table.
SELECT * FROM student WHERE LOCATE(2,id) > 0
We will get 14 matching records as all records having id equal to 2, 12, 20,21, ..... 32,42 will be retured. The matching is done by using the ID field as string.
SELECT * FROM student WHERE name IN ( 'John','Alex')
While using SQL_FIND_IN we use one keyword on a column like this.
SELECT * FROM student WHERE FIND_IN_SET('Bigy',name)
We get one matching record , output is here
14 Bigy Seven 88 female
Now let us apply this LOCATE command to search for the presence of the name john in the name field (column ) of our student table.
SELECT * FROM `student` WHERE locate( 'john', name )
The output of this query is here.
name | class | mark | sex |
John Deo | Four5 | 75 | male |
John Mike | Four5 | 60 | male |
Alex John | Four5 | 55 | male |
My John Rob | Fifth5 | 78 | male |
Big John | Four5 | 55 | male |
Babby John | Four5 | 69 | male |
SELECT * FROM `student` WHERE FIND_IN_SET('john',name)
The above command will return empty results set. Here the string 'john' is matched with all data in name column of student table.