MySQL FIND_IN_SET()

FIND_IN_SET(search, list) returns the 1-based position of an exact item inside a comma-separated string. It returns 0 when the item is not present and NULL if either argument is NULL.

SELECT FIND_IN_SET('b', 'a,b,c,d');

Exact items in a comma-separated list

SELECT FIND_IN_SET('ry', 'kpy,tyu,ryk,pkr');

ry is not an exact list item in this example, so the result is 0.

SELECT FIND_IN_SET('ry', 'kpy,tyu,ryk,pkr,bry,ry,lkiu');

Here ry is the sixth item.

Using FIND_IN_SET() with a CSV-style column

SELECT * FROM profiles WHERE FIND_IN_SET('sql', skills) > 0;

This pattern is appropriate only when the column actually stores comma-separated values. For normalized relational data, a separate related table is usually better design.

SELECT FIND_IN_SET('Alex John', 'Max Ruin,Alex John,John Deo');

Example output:

2

FIND_IN_SET() and LOCATE()

SELECT FIND_IN_SET('ry', 'kpy,tyu,ryk,pkr');

SELECT LOCATE('ry', 'kpy,tyu,ryk,pkr');

FIND_IN_SET() counts exact comma-separated items; LOCATE() searches for a substring and returns its character position.

SELECT FIND_IN_SET('2', '1,2,3');

Output:

2

SELECT LOCATE('2', '12,20,32');

For broader text search, compare keyword search and REGEXP. For additional position/list functions, compare FIELD(), SUBSTRING_INDEX(), and REPLACE().

Difference from IN and LIKE

SELECT * FROM student WHERE name IN ('John', 'Alex');

IN() compares one value with a list of SQL values.

SELECT FIND_IN_SET('Bigy', 'John,Bigy,Alex');

Output:

2

Example substring matches from the student table:

For substring search inside a normal text column, use LIKE or LOCATE(), not FIND_IN_SET().

SELECT * FROM student WHERE name LIKE '%john%';

SELECT FIND_IN_SET('john', 'Alex John');

The last query returns 0 because 'Alex John' is one list item, not a comma-separated list containing the exact item 'john'.

Student table reference | Download the student table SQL dump

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: