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