Find_in_set returns an empty set?

Question

I have some questions about the set type in find_in_set here is the code:

create table set_test(id int,set_col SET('a','b','c','d'));
insert into set_test(id,set_col) values(1,'a,b'),(2,'a,b,b');
select * from set_test where find_in_set('a,b',set_col)

return empty set!!! why?

Answer 1

you can not use find_in_set function to search for a string with a comma in it 'a,b', but only for any string which separated by comma like a or b or c, so if you try this will work correctly:

select * from set_test where find_in_set('a',set_col); 

but in your case you may use like:

select * from set_test where set_col like '%a,b%';

Answer 2

The documentation of function FIND_IN_SET():

FIND_IN_SET(str, strlist)

Returns a value in the range of 1 to N if the string str is in the string list strlist consisting of N substrings. A string list is a string composed of substrings separated by “,” characters.

Its second argument is a string containing values separated by comma. The value you passed to FIND_IN_SET() (the column set_col having the type SET) matches this description. However, its first argument should be only one of the values contained in the list. a,b is not a valid value to pass as first argument to FIND_IN_SET() as it contains to values: a and b. You should pass it either 'a' or 'b' if you want it to find something.

This is also documented at the end of the paragraph which says:

This function does not work properly if the first argument contains a comma (“,”) character.

Answer 3

The find_in_set function returns a numeric value of the search result, or 0 if not found.

You should change: select * from set_test where find_in_set('a,b',set_col);

To: select * from set_test where find_in_set('a,b',set_col) > 0;