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
FIND_IN_SET & LOCATE
We have already seen FIND_IN_SET against string. We will find the difference between FIND_IN_SET and LOCATE query.
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
Here keyword is not matched against each set of string, it is matching as a total string so the position of matching string ry is located at 9th position from left. Here position of (, ) coma is also considered as one char for calculating the postion of matching string.
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.
Difference with IN
While using IN Query , We search for a set of key words on a column like this
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)