MySQL FIND_IN_SET() Function

 The FIND_IN_SET() function in MySQL is used to find the position of a string in a comma-separated list of strings. It is commonly used when working with a single column containing delimited values.

Syntax:

FIND_IN_SET(str, strlist)

• str: The string to search for.
• strlist: The comma-separated list of strings to search within.

Returns:

• The position (1-based index) of str if it is found in strlist.
• 0 if str is not found in strlist.
• NULL if either str or strlist is NULL.

Key Notes:

• FIND_IN_SET() does not work with strings that contain commas as part of the value because the function assumes a comma-separated list.
• The search is case-sensitive.

Example Usage:

Example 1: Basic Search

SELECT FIND_IN_SET('apple', 'apple,banana,cherry');
-- Output: 1 (because 'apple' is the first element)

Example 2: String Not Found

SELECT FIND_IN_SET('orange', 'apple,banana,cherry');
-- Output: 0 (because 'orange' is not in the list)

Example 3: NULL Values

SELECT FIND_IN_SET(NULL, 'apple,banana,cherry');
-- Output: NULL (because the first argument is NULL)

SELECT FIND_IN_SET('apple', NULL);
-- Output: NULL (because the second argument is NULL)

Example 4: Dynamic Use with a Column

SELECT id, FIND_IN_SET('apple', items) AS position
FROM my_table
WHERE FIND_IN_SET('apple', items) > 0;

This query returns rows where the column items (a comma-separated string) contains the value 'apple'.

---

Limitations:

• It is not optimized for large datasets or frequent use. For better performance and normalized database design, consider using a separate table for such relationships instead of storing comma-separated values.