MySQL find_in_set with multiple search string

MySQL find_in_set with multiple search string

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

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

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 cant 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 cant find a string a,b,c in any SET by definition – it contains commas.

MySQL find_in_set with multiple search string

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

Leave a Reply

Your email address will not be published. Required fields are marked *