How to Find Value in a Set in MySQL Database

1. Create a Table in MySQL
2. Use FIND_IN_SET() to Find a Value in a Set in a MySQL Database

In this tutorial, we aim to explore how to check a value’s occurrence or find a value in a set in a MySQL database.

This can be done with the help of either the IN() function or the FIND_IN_SET() function. Let us explore using the FIND_IN_SET() function in the MySQL database.

The FIND_IN_SET() function mainly takes in two arguments. The first argument is the value to be searched, and the second is the set where the value is to be searched.

This can be illustrated as FIND_IN_SET("search", {search_here}). Let us try to use this function in our MySQL server.

Create a Table in MySQL

Before we begin, we will create a dummy dataset to work with. Here we will create a table, student_details, along with a few rows.

-- create the table student_details
CREATE TABLE student_details(
  stu_id int,
  stu_firstName varchar(255) DEFAULT NULL,
  stu_lastName varchar(255) DEFAULT NULL,
  primary key(stu_id)
);
-- insert rows to the table student_details
INSERT INTO student_details(stu_id,stu_firstName,stu_lastName)
 VALUES(1,"Preet","Sanghavi"),
 (2,"Rich","John"),
 (3,"Veron","Brow"),
 (4,"Geo","Jos"),
 (5,"Hash","Shah"),
 (6,"Sachin","Parker"),
 (7,"David","Miller");

The above query creates a table with rows containing the students’ first and last names. To view the entries in the data, we use the following code:

SELECT * FROM student_details;

The above code would give the following output:

stu_id  stu_firstName   stu_lastName
1         Preet         Sanghavi
2         Rich          John
3         Veron         Brow
4         Geo           Jos
5         Hash          Shah
6         Sachin        Parker
7         David         Miller

Use FIND_IN_SET() to Find a Value in a Set in a MySQL Database

We have successfully created our table student_details and visualized it. Let us try to find a particular name in the set of stu_firstName.

The syntax for carrying out the task mentioned above can be illustrated as follows:

SELECT FIND_IN_SET("value_to_be_searched", {set});

Here, as we can see, the term value_to_be_searched will be replaced by the actual value we need to look for in our table.

Let us identify if the value David exists in the stu_firstName column. This can be done with the help of the following query:

SELECT FIND_IN_SET("David", stu_firstName) as boolean_here from student_details ;

The output of the code mentioned above can be illustrated as follows:

boolean_here
0
0
0
0
0
0
1

This function, however, is available only in MySQL version 4.0 and later. Therefore, we have successfully understood how to use the FIND_IN_SET() function in MySQL.