FIND_IN_SET() Function in MySQL

The FIND_IN_SET() function in MySQL is a powerful tool for searching within comma-separated lists of strings. It allows you to locate the position of a specific string within a list and is commonly used in cases where lists are stored as strings within a database.

In this article, We will learn about FIND_IN_SET() Function in MySQL in detail.

FIND_IN_SET()

• The FIND_IN_SET() function in MySQL is used to search for a string within a comma-separated list of strings.
  
• It returns the position (index) of the string in the list, where the index starts at 1.
  
• If the string is not found, it returns 0.
  
• This function is useful when working with strings that represent lists or sets and we need to locate specific values within those lists.

Points to be noted:

• If a string is not found in string_list, the result is 0
• If a string or string_list is NULL, the result is NULL
• If a string_list is an empty string (""), the result is 0

Syntax:

FIND_IN_SET("string", "string_list")

Note: Parameter string is mandatory to search for string_list;

string_list is list of string values.

Example 1:

Search for "a" within the list of strings:

SELECT FIND_IN_SET("a", "g, e, e, k, s, f, o, r, g, e, e, k, s"); 

Result:

Example 2:

Search for "q" within the list of strings (string list is NULL) :

SELECT FIND_IN_SET("a", null); 

Result:

Example 3:

Search for "q" within the list of strings :

SELECT FIND_IN_SET("g", "g, e, e, k, s, f, o, r, g, e, e, k, s"); 

Result:

Conclusion

The FIND_IN_SET() function is a valuable tool when working with comma-separated values in MySQL. It efficiently identifies the position of a specific string within a list, providing an easy method for searching and indexing.