FIND_IN_SET mysql query to get position of searched string within a set of strings separated by coma

SELECT FIND_IN_SET('search_string','set1,set2,set3')

Here search_string is used to search on on set of strings separated by coma.
Output

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

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)

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

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

SQL String References FIELD to get position of string substring_index to get part of string using delimiter

Subscribe to our YouTube Channel here

SQL FIND_IN_SET command to search strings within a set of string

Example of FIND_IN_SET on string

FIND_IN_SET & LOCATE

Difference with IN

Subscribe