Geek Answers Handbook

Mysql WHERE SET datatype contains an element

I have a table that uses the SET data type for one of the fields and to check if the field contains a specific item that I use

SELECT * FROM table WHERE myset LIKE %value%;

This works most of the time, but two of the potential meanings have the same word, i.e. one of the possible elements in the set is a poodle, and the other is a toy poodle. If i do

 SELECT * FROM table WHERE myset LIKE %Poodle%;

It returns all rows containing a poodle or poodle. I want it to return only if the field contains a poodle. If I remove the wildcards, it will return lines that contain ONLY a poodle. So basically, if the table was:

 id | myset ------------------------- 1 | "Poodle" 2 | "Toy Poodle" 3 | "Poodle","Toy Poodle" 4 | "Toy Poodle","Poodle"

I need a select statement that will return 1,3 and 4, but not 2. Is this possible?

+6
mysql
source share
4 answers

How to use the FIND_IN_SET method?

+8
source share

You need to do: SELECT * FROM table WHERE FIND_IN_SET('Poodle',myset)>0 , as described in the documentation

+11
source share
 SELECT * FROM table WHERE FIND_IN_SET( '"Poddle"', myset ) > 0

search " will be important to eliminate entry # 2

+1
source share

Sorry if I completely skip the point here, but are the quotes in your result set actually stored in db?

what do I mean if the actual value of the first line matches ...

 "Poodle"

... or you just specified these values ​​for presentation purposes, in which case the first value will really be ...

 Poodle

The only reason I'm asking is why not just make a request like ...

 SELECT * FROM table WHERE myset LIKE '%"Poodle"%';

Sorry if I massively missed the point or too simplified beyond recognition.

good luck with fixing anyway!

0
source share

More articles:


All Articles