MySQL find_in_set with multiple search string

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?

Contents hide

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 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),"

Method 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.

Method 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

Method 4

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, ',', '|'), '),') ";

Method 5

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.

Method 6

You can also use the like command for instance:

where setcolumn like '%a,b%'

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

which might work in some situations.

Method 7

you can use in to find match values from two values

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

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating