I find that find_in_set only search by a single string :-
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 :-
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?
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.
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
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, ',', '|'), '),') ";
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;
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;
3 which is both
Your sets can still be queried using the other methods like REGEXP , LIKE, FIND_IN_SET(), and so on. Use whatever you need.
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.
you can use in to find match values from two values
SELECT * FROM table WHERE myvals in (a,b,c,d)