How to Use FIND_IN_SET in MySQL: Syntax and Examples

Use the FIND_IN_SET(str, strlist) function in MySQL to find the position of str within a comma-separated list strlist. It returns the index (starting at 1) if found, or 0 if not found. This is useful for searching values inside strings that store lists separated by commas.

Syntax

The syntax of FIND_IN_SET is simple:

• str: The string you want to find.
• strlist: A comma-separated list of strings.

The function returns the position of str in strlist starting at 1, or 0 if str is not found.

sql

FIND_IN_SET(str, strlist)

Example

This example shows how to find the position of 'apple' in a comma-separated list:

sql

SELECT FIND_IN_SET('apple', 'banana,apple,orange') AS position;

-- Using in a table to filter rows where 'apple' is in the list
CREATE TEMPORARY TABLE fruits (id INT, fruit_list VARCHAR(100));
INSERT INTO fruits VALUES (1, 'banana,apple,orange'), (2, 'grape,melon'), (3, 'apple,pear');

SELECT id, fruit_list
FROM fruits
WHERE FIND_IN_SET('apple', fruit_list) > 0;

Output

position
2

id | fruit_list
1  | banana,apple,orange
3  | apple,pear

Common Pitfalls

Common mistakes when using FIND_IN_SET include:

• Passing a list with spaces after commas, which causes no match because spaces are considered part of the string.
• Using FIND_IN_SET with NULL values returns NULL, not 0.
• Trying to use it with non-string types without casting.

Always ensure the list has no spaces and the searched string matches exactly.

sql

/* Wrong: spaces in list prevent match */
SELECT FIND_IN_SET('apple', 'banana, apple,orange') AS wrong_position;

/* Right: no spaces in list */
SELECT FIND_IN_SET('apple', 'banana,apple,orange') AS correct_position;

Output

wrong_position
0

correct_position
2

Quick Reference

Usage	Description
FIND_IN_SET('str', 'a,b,c')	Returns position of 'str' in list or 0 if not found
FIND_IN_SET('apple', column_name)	Find 'apple' in comma-separated values stored in a column
Returns 0	If 'str' is not in the list
Returns NULL	If either argument is NULL

Key Takeaways

FIND_IN_SET returns the 1-based position of a string in a comma-separated list or 0 if not found.

Ensure the list has no spaces after commas to avoid mismatches.

FIND_IN_SET returns NULL if any argument is NULL.

Use FIND_IN_SET to filter rows where a value exists in a CSV string column.

It works only with strings, so cast other types if needed.