0

Not sure how to explain this. I have the following sample table

id          participants
13          128, 125
18          122, 125
29          182, 125
34           17, 12
38           18, 15

I want to get a count of the messages where 12 is on the right so am using the following query, select count(id) as messages from table where locate (12, participants, 2) The problem with this query is that it returns all results with 12 e.g. 125, so instead of having one count i have 4 counts which is incorrect. Any suggestions on what query to use?

mr_j
  • 125
  • 1
  • 15
  • 1
    you don't. you normalize your tables so the problem goes away. put your participants into a child table, one person to record. – Marc B Aug 18 '14 at 17:08

2 Answers2

0

You can get your result with the help of SUBSTRING_INDEX and ', ' as delimiter:

SELECT * FROM your_table
WHERE
    SUBSTRING_INDEX(participants, ', ', -1) = 12;

If you can't be sure about the blank after the comma, you could use 

SELECT * FROM your_table
WHERE
    TRIM(SUBSTRING_INDEX(participants, ',', -1)) = 12;

instead.

Demo

Note
It would be better not to store delimited lists of values in just one field, if you can. It's asking for trouble and bad performance, because MySQL can't use an index for this kind of condition. It's sound advice of Marc to normalize your tables, be rid of this problem and gain performance by use of an index.

VMai
  • 10,156
  • 9
  • 25
  • 34
0

FIND_IN_SET() is used for this kind of thing, but you'll need to strip out the spaces too, since those shouldn't be in there. Here, I used REPLACE() to remove the spaces:

SELECT * FROM example
WHERE FIND_IN_SET('12', REPLACE(participants, ' ', ''))
Marcus Adams
  • 53,009
  • 9
  • 91
  • 143