MySQL - FIND_IN_SET() Function

The MySQL FIND_IN_SET() function is used to search for a specific string within a list, which is a string containing substrings separated by commas.

This function accepts two parameters: the string or number to search for and the list of strings or numbers in which the search is performed.

Syntax

Following is the syntax of MySQL FIND_IN_SET() function −

FIND_IN_SET(string,stringlist)

Parameters

This function takes a search string and a comma-separated list of strings as parameter.

Return Value

This function returns the index of the first occurrence of the search string in the list or 0 if not found.

Example

In the following example, we are using the FIND_IN_SET() function to find the position of 'JavaFX' in a list of programming languages −

SELECT FIND_IN_SET('JavaFX', 'Java,JavaFX,OpenCV,WebGL');

Following is the output of the above code −

FIND_IN_SET('JavaFX', 'Java,JavaFX,OpenCV,WebGL')
2

Example

If we search for a string that is not there in the list of strings, the field_in_set() function returns 0 −

SELECT FIND_IN_SET('unknown', 'Java,JavaFX,OpenCV,WebGL');

Output of the above code is as shown below −

FIND_IN_SET('unknown', 'Java,JavaFX,OpenCV,WebGL')
0

Example

If we search for a NULL value in the list, the FIND_IN_SET() function returns NULL −

SELECT FIND_IN_SET(NULL, 'Java,JavaFX,OpenCV,WebGL');

The output obtained is as follows −

FIND_IN_SET(NULL, 'Java,JavaFX,OpenCV,WebGL')
NULL

Example

You can also pass list of numbers as arguments to this function and search for desired value −

SELECT FIND_IN_SET('25',' 225,669,557,25,66');

The result produced is as follows −

FIND_IN_SET('25',' 225,669,557,25,66')
4

Example

Let us create a table named "STUDENT_TABLE" and insert records into it using CREATE and INSERT statements as shown below −

CREATE TABLE STUDENT_TABLE (
   id INT NOT NULL AUTO_INCREMENT,
   name VARCHAR(50) NOT NULL,
   courses VARCHAR(100) NOT NULL,
   PRIMARY KEY (id)
);

Now, let us insert records into it using the INSERT statement −

INSERT INTO STUDENT_TABLE (name, courses) VALUES
('Varun', 'Math,Science,English'),
('Vaidhya', 'Science,Art'),
('Siddhu', 'Math,History,Art'),
('Priya', 'English,History'),
('Nikhil', 'Science,Math');

The STUDENT_TABLE obtained is as follows −

id name courses
1 Varun Math,Science,English
2 Vaidhya Science,Art
3 Siddhu Math,History,Art
4 Priya English,History
5 Nikhil Science,Math

Following query retrieves all students who are enrolled in 'Math' course using the MySQL FIND_IN_SET() function −

SELECT name, courses FROM STUDENT_TABLE 
WHERE FIND_IN_SET('Math', courses);

After executing the above code, we get the following output −

name courses
Varun Math,Science,English
Siddhu Math,History,Art
Nikhil Science,Math
mysql-find-in-set-function.htm
Advertisements