MySQL FIND_IN_SET with Multiple Search Strings

Introduction

This post explains how to search for multiple strings in MySQL without using the FIND_IN_SET() function.

In MySQL, the FIND_IN_SET() function can be used to search for a single string within a comma-separated list.

Example:

find_in_set('a','a,b,c,d')

However, searching for multiple values directly like the following is not possible:

find_in_set('a,b,c,d','a,b,c,d')

If we want to search for multiple values using FIND_IN_SET(), we need to use multiple conditions:

find_in_set('a', 'a,b,c,d') OR find_in_set('b', 'a,b,c,d') OR find_in_set('c', 'a,b,c,d')

Using this format can become difficult to implement in multiple places. The following approach provides a simple way to search for multiple values without using the FIND_IN_SET() function.

Prerequisites

Before implementing the query, you should have:

• MySQL installed and configured.
• Access to the MySQL database.
• A table containing the values you want to search.
• Basic knowledge of SQL queries.
• Basic knowledge of PHP if using the PHP example.

Implementation

Step 1: Search Multiple Values Using MySQL

The following MySQL query can be used to search for multiple values without using the FIND_IN_SET() function:

SELECT * from table_name WHERE CONCAT(",", `id`, ",") REGEXP ",(1|2|3),"

Here, the REGEXP expression allows multiple values to be specified using the | operator.

For example:

1|2|3

means that the query searches for 1, 2, or 3.

Step 2: Using FIND_IN_SET() for Comparison

With FIND_IN_SET(), multiple search values would require separate conditions:

find_in_set('a', 'a,b,c,d') OR find_in_set('b', 'a,b,c,d') OR find_in_set('c', 'a,b,c,d')

The alternative query avoids repeating FIND_IN_SET() for every search value:

SELECT * from table_name WHERE CONCAT(",", `id`, ",") REGEXP ",(1|2|3),"

This can make the query easier to construct when multiple values need to be searched.

PHP with MySQL Without FIND_IN_SET()

If the search values are available as an array in PHP, the query can be constructed dynamically.

Example

Assume the following array values:

$a=array(10,12,13,14,15);

The query can be generated using the following code:

<?php

$a=array(10,12,13,14,15);
$test="SELECT * from table_name WHERE";
$tot=count($a);
$counter=1;
foreach($a as $val)
{
 echo $counter;
 $test .= " id=$val";
 if($counter !=$tot)
 {
 $test .=" OR ";
 }
 $counter++;
} 
echo $test;
 mysql_query($test); ?>

The PHP code loops through the array values and adds each value to the SQL query using an OR condition.

Finally, the generated query will look similar to:

SELECT * from table_name WHERE id= 10 OR id = 12 OR id= 13 ...

After the query is generated, it can be executed to retrieve the matching results.

Conclusion

MySQL’s FIND_IN_SET() function is useful for searching a single value in a comma-separated list, but searching multiple values can require multiple FIND_IN_SET() conditions.

Using CONCAT() with REGEXP provides another approach for searching multiple values, while PHP can be used to dynamically build an SQL query when the values are available in an array.

FAQs

What is FIND_IN_SET() in MySQL?

FIND_IN_SET() is a MySQL function used to search for a string within a comma-separated list of values.

Can FIND_IN_SET() search multiple values directly?

No. Multiple values generally require separate FIND_IN_SET() conditions connected using operators such as OR.

How can I search multiple values without FIND_IN_SET()?

You can use CONCAT() “together with” REGEXP to match multiple values in the query.

Can PHP arrays be used to build a MySQL query?

Yes. PHP arrays can be processed using a loop to construct multiple SQL conditions dynamically.

Why use REGEXP multiple search values?

REGEXP allows multiple matching alternatives to be specified using the | operator, making it possible to search for several values in a single expression.

Related Articles

Change WordPress Site URL from Backend Using MySQL/MariaDB

Learn how to change the WordPress site URL directly from the MySQL/MariaDB database when the site URL needs to be updated from the backend.

Change WordPress Site URL Backend Using MySQL/MariaDB

MySQL FIND_IN_SET with Multiple Search Strings

This article provides another reference for working with MySQL FIND_IN_SET() and handling multiple search strings.

MySQL FIND_IN_SET Multiple Search Strings

Talk to our experts

Looking for the right technology solution for your business? Our team of experts can help you with development, cloud, DevOps, design, and a wide range of other technology needs. Get in touch with our team here.