Here we are going to show how you can use FIND_IN_SET in where clause for searching records. Sometime we store comma separated data in column and want to get records where particular value exist in column. In this situation we use Search Records Using FIND_IN_SET.
Below is sample table with data
CREATE TABLE `student` (
`id` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
`subject` varchar(255) NOT NULL,
`address` varchar(255) NOT NULL,
`gender` varchar(255) NOT NULL,
`class` varchar(255) NOT NULL,
`age` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `student` (`id`, `name`, `subject`, `address`, `gender`, `class`, `age`) VALUES
(1, 'Sanjay', 'Math,Biology,Hindi', 'Noida', 'Male', '12th', 18),
(2, 'Devendra', 'Physics,Math,English', 'Noida', 'Male', '10th', 17),
(3, 'Rajendra', 'Chemistry,Biology,English', 'Rampur', 'Male', '12th', 21),
(4, 'Rani', 'Social Science,English,Hindi', 'Delhi', 'Female', '9th', 13),
(5, 'Saroj', 'English,Physics', 'Rampur', 'Female', '12th', 17),
(6, 'Arvind', 'Hindi,Physics', 'Noida', 'Male', '10th', 16),
(7, 'Mahesh', 'Math,Hindi', 'Rampur', 'Male', '12th', 20),
(8, 'Kuldeep', 'Physics,Chemistry,Math', 'Noida', 'Male', '12th', 19),
(9, 'Amit', 'English,Biology,Hindi', 'Rampur', 'Male', '12th', 18),
(10, 'Navdeep', 'Biology,Physics,Chemistry', 'Noida', 'Male', '10th', 15),
(11, 'Vedant', 'Biology,Chemistry', 'Delhi', 'Male', '9th', 14),
(12, 'Mukul', 'English,Hindi', 'Delhi', 'Male', '12th', 17);
ALTER TABLE `student`
ADD PRIMARY KEY (`id`);
ALTER TABLE `student`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=13;
Here we want to find how many students subject is Hindi.
Below is query:
SELECT * FROM `student` WHERE FIND_IN_SET('Hindi',`subject`);
Below is result:
I hope this article helps you.
Thanks for visiting Inflay.com .