CODEWITHSUNDEEP
mysql
awestover89
awestover89

Reputation: 1763

mysql WHERE SET datatype contains item

I have a table that uses a SET datatype for one of the fields, and to check if the field contains a specific element I use

SELECT * FROM table WHERE myset LIKE %value%;

This works most of the time, but two of the potential values have the same word, i.e. one possible element in the set is Poodle and another is Toy Poodle. If I do 

SELECT * FROM table WHERE myset LIKE %Poodle%;

It returns all rows that have either Poodle or Toy Poodle. I want it to only return if the field contains Poodle. If I remove the wildcards then it will only return the rows that have ONLY 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 would return 1,3, and 4 but not 2. Is this possible?

Upvotes: 7

Views: 12965

Answers (4)

Nobwyn
Nobwyn

Reputation: 8685

You need to do:

SELECT * FROM table WHERE FIND_IN_SET('Poodle',myset)>0

...as described in documentation

Upvotes: 17

BizNuge
BizNuge

Reputation: 938

sorry if I'm completely missing the point here, but are the quotations in your resultset actually stored in the db?

what I mean is, does the actual value of the first row equal...

"Poodle"

...or have you just quoted these values for presentation purposes, in which case the first value would actually be... 

Poodle

Only reason I ask is, why not just do a query like...

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

Apologies if I've massively missed the point or over-simplified beyond the point of recognition.

good luck with a fix anyway!

Upvotes: 1

bensiu
bensiu

Reputation: 25564

SELECT * FROM table WHERE FIND_IN_SET( '"Poddle"', myset ) > 0

looking for " would be important to eliminate record #2

Upvotes: 2

Ali Lown
Ali Lown

Reputation: 2331

How about using the FIND_IN_SET method?

Upvotes: 9

Related Questions