MySQL find_in_set with multiple search string

Question

I find that find_in_set only search by a single string :-

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

In the above example, 'a' is the only string used for search.

Is there any way to use find_in_set kind of functionality and search by multiple strings, like :-

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

In the above example, I want to search by three strings 'a,b,c'.

One way I see is using OR

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

Is there any other way than this?

Transcend

Answer 1

there is no native function to do it, but you can achieve your aim using following trick

WHERE CONCAT(",", `setcolumn`, ",") REGEXP ",(val1|val2|val3),"

Inn

And for rails it works as Model.where('CONCAT(",", setcolumn, ",") REGEXP ?', [val1,val2,val3].join('|')) – Hathor

What if my val1|val2|val3 are not in order as the DB entries. – Slavish

Amazing answer! - saves me seconds on my query replacing the find_in_set You can eve do this for 2 tables T1 and T2: .... where CONCAT(",", T1.setcolumn, ",") REGEXP CONCAT(",(", REPLACE(T2.setcolumn, ',', '|'), "),") – Cianca

Hi, this is working in Mysql. But in PHP i added same. its throwing error for "syntax error, unexpected ',' in Line" – Restrained

@AbijithAjayan I've solved same problem with adding Allow User Variables=true in the connection string – Astor

This works great, saved me from stucking in Waiting for table lock and Copying to tmp table freezes...Though, i use it at product filtering and OR is not so good for "filtering"..Any idea how i could use the optimisation for "AND"..before i was using JOIN for each and every Option selected, which resulted in 5-7 different JOINs in same table for different FIND_IN_SET (optionid, "in optionids")... – Raki

Nice solution, but I found it to be significantly slower than concatenating FIND_IN_SET with OR – Hanselka

Answer 2

The MySQL function find_in_set() can search only for one string in a set of strings.

The first argument is a string, so there is no way to make it parse your comma separated string into strings (you can't use commas in SET elements at all!). The second argument is a SET, which in turn is represented by a comma separated string hence your wish to find_in_set('a,b,c', 'a,b,c,d') which works fine, but it surely can't find a string 'a,b,c' in any SET by definition - it contains commas.

Ejector

Yes, you are right. I think The only solution for my problem will be using OR and finset. Like "find_in_set('a', 'a,b,c,d') OR find_in_set('b', 'a,b,c,d') OR find_in_set('b', 'a,b,c,d')". – Transcend

Answer 3

You can also use this custom function

CREATE FUNCTION SPLIT_STR(
  x VARCHAR(255),
  delim VARCHAR(12),
  pos INT
)
RETURNS VARCHAR(255)
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
       LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
       delim, ''); 

DELIMITER $$
    CREATE FUNCTION `FIND_SET_EQUALS`(`s1` VARCHAR(200), `s2`  VARCHAR(200)) 
    RETURNS TINYINT(1)
    LANGUAGE SQL
    BEGIN
          DECLARE a INT Default 0 ;
            DECLARE isEquals TINYINT(1) Default 0 ;
          DECLARE str VARCHAR(255);
          IF s1 IS NOT NULL AND s2 IS NOT NULL THEN
             simple_loop: LOOP
                 SET a=a+1;
                 SET str= SPLIT_STR(s2,",",a);
                 IF str='' THEN
                    LEAVE simple_loop;
                 END IF;
                 #Do  check is in set
                 IF FIND_IN_SET(str, s1)=0 THEN
                    SET isEquals=0;
                     LEAVE simple_loop;
                 END IF;
                 SET isEquals=1;
            END LOOP simple_loop;
          END IF;
        RETURN isEquals;
    END;
    $$
    DELIMITER ;

SELECT FIND_SET_EQUALS('a,c,b', 'a,b,c')- 1
SELECT FIND_SET_EQUALS('a,c', 'a,b,c')- 0
SELECT FIND_SET_EQUALS(null, 'a,b,c')- 0

Tucker

Answer 4

Wow, I'm surprised no one ever mentioned this here.
In a nutshell, If you know the order of your members, then just query in a single bitwise operation.

SELECT * FROM example_table WHERE (example_set & mbits) = mbits;

Explanation:

If we had a set that has members in this order: "HTML", "CSS", "PHP", "JS"... etc.
That's how they're interpreted in MySQL:

"HTML" = 0001 = 1
"CSS"  = 0010 = 2
"PHP"  = 0100 = 4
"JS"   = 1000 = 16

So for example, if you want to query all rows that have "HTML" and "CSS" in their sets, then you'll write

SELECT * FROM example_table WHERE (example_set & 3) = 3;

Because 0011 is 3 which is both 0001 "HTML" and 0010 "CSS".

Your sets can still be queried using the other methods like REGEXP , LIKE, FIND_IN_SET(), and so on. Use whatever you need.

Episodic

Answer 5

Amazing answer by @Pavel Perminov! - And also nice comment by @doru for dynamically check..

From there what I have made for PHP code CONCAT(',','" . $country_lang_id . "', ',') REGEXP CONCAT(',(', REPLACE(YourColumnName, ',', '|'), '),') this below query may be useful for someone who is looking for ready code for PHP.

$country_lang_id = "1,2";

$sql = "select a.* from tablename a where CONCAT(',','" . $country_lang_id . "', ',') REGEXP CONCAT(',(', REPLACE(a.country_lang_id, ',', '|'), '),') ";

Dovev

Answer 6

If you want to match all values instead of OR you should use AND

FIND_IN_SET('a', 'a,b,c,d') > 0 AND FIND_IN_SET('b', 'a,b,c,d') > 0 AND FIND_IN_SET('c', 'a,b,c,d') > 0

In my case SELECT * FROM images WHERE (FIND_IN_SET('illustration', tags) > 0 AND FIND_IN_SET('creative', tags) > 0 AND FIND_IN_SET('design', tags) > 0 AND FIND_IN_SET('drawing', tags) > 0);

Moffat

Answer 7

You can also use the like command for instance:

where setcolumn like '%a,b%'

or

where 'a,b,c,d' like '%b,c%'

which might work in some situations.

Adopted

how can you use [where 'a,b,c,d' like '%b,c%'] it may be 'c,b' also. So I think it's not the good solution! – Argilliferous

Answer 8

you can use in to find match values from two values

SELECT * FROM table WHERE  myvals in (a,b,c,d)

Goggin

IN clause does not take a variable. It uses the column w3resource.com/sql/in-operator/sql-in-operator.php – Coolie