SQL FIND_IN_SET command to search strings within a set of string

SELECT FIND_IN_SET('search_string','set1,set2,set3')
Here search_string is used to search on on set of strings separated by coma.

0 : if no match is found
number : position of the matching set
null : If search is applied on a null set

Example of FIND_IN_SET on string

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


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)
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.
John DeoFour575male
John Mike Four5 60 male
Alex John Four5 55 male
My John RobFifth5 78male
Big John Four5 55 male
Babby John Four5 69 male
As you can see we have collected all the records having 'john' in any place in the name column of the student table.
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.
Download sql dump of this student table
Visitors Rating
Your Rating


Post Comment This is for short comments only. Use the forum for more discussions.

HTML . MySQL. PHP. JavaScript. ASP. Photoshop. Articles. FORUM Contact us
©2000-2018 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer