How to Search Comma-Separated Values in MySQL Using FIND_IN_SET()

How to Search Comma-Separated Values in MySQL Using FIND_IN_SET()

Learn to search comma-separated fields in MySQL databases using FIND_IN_SET(). Perfect for developers working with non-normalized data structures.

🔍 How to Search Comma-Separated Values in MySQL Using FIND_IN_SET()

When working with databases, it's best practice to normalize your data — especially when storing multiple values. However, in real-world scenarios (like legacy systems or quick projects), developers often store multiple values in a comma-separated format in a single field.

In this blog, we'll show you how to extract specific data from such fields using MySQL's built-in function FIND_IN_SET() with a practical example using PHP and MySQLi.

🩺 Example Scenario: Doctor Specializations

Let's say you have a table named doctorsdb_doctors with a structure like this:

You want to fetch all doctors who offer Pediatric Care.

✅ The MySQL Query

Here's how to use FIND_IN_SET() in your SQL:

SELECT * 
FROM `doctorsdb_doctors` 
WHERE FIND_IN_SET('Pediatric Care', doctors_specilization);

This query will return any row where 'Pediatric Care' exists as a distinct value in the doctors_specilization field, regardless of its position.

💻 Using MySQLi in PHP

Here’s how you can run this query in PHP using MySQLi:

<?php
$mysqli = new mysqli("localhost", "username", "password", "database");

// Check connection
if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}

$specialization = 'Pediatric Care';

$sql = "SELECT * FROM doctorsdb_doctors WHERE FIND_IN_SET(?, doctors_specilization)";
$stmt = $mysqli->prepare($sql);
$stmt->bind_param("s", $specialization);
$stmt->execute();
$result = $stmt->get_result();

while ($row = $result->fetch_assoc()) {
    echo "Doctor Name: " . $row['doctors_name'] . "<br>";
    echo "Specializations: " . $row['doctors_specilization'] . "<br><br>";
}

$stmt->close();
$mysqli->close();
?>

🧾 Summary

• • FIND_IN_SET() is a handy MySQL function to search for a value in a comma-separated list.
  • It's useful for simple filtering in flat data structures.
    
  • For complex applications, consider proper data normalization.