CODEWITHSUNDEEP
mysqlfind-in-set
dbTry
dbTry

Reputation: 13

Find_in_set returns an empty set?

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?

Upvotes: 0

Views: 1062

Answers (3)

Les Brinkworth
Les Brinkworth

Reputation: 1

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;

Upvotes: 0

axiac
axiac

Reputation: 72266

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.

Upvotes: 0

Gouda Elalfy
Gouda Elalfy

Reputation: 7023

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%';

Upvotes: 1

Related Questions