CODEWITHSUNDEEP
mysqlcomparison
Oliver Williams
Oliver Williams

Reputation: 6334

Mysql compare comma-separated field with single string

So a field called schools in the database might have a value of:

'13,121,112,1212'

I'm using that to show the potential for a mistake.

Suppose I'm looking for a value of 12 in that field. The commas denote a "whole number" and I don't want to match 112 or 1212

Is there a more elegant match than this?

@compare = 12;
WHERE CONCAT(schools,',') LIKE CONCAT('%',compare,',%)

I was recently impressed by the GROUP_CONCAT function but this is kind of in reverse of that. Thanks!

Upvotes: 2

Views: 4011

Answers (1)

Joachim Isaksson
Joachim Isaksson

Reputation: 180887

For this simple case you can use FIND_IN_SET();

WHERE FIND_IN_SET('13', schools);

Note though that there is no good indexing for columns with comma separated text, so the queries will be much slower than a normalized database.

Upvotes: 8

Related Questions