Detect duplicate values in a comma separated field in mysql

Question

Is it possible somehow to return all the rows in a mysql table where one field has duplicate values in a comma separated string?

EG if we had fields

ID    VALUE
'1', '123,123,678'
'2', '23,24,25'

I only want to return row 1 in this instance?

Answer 1

There is no way to do this from within SQL that's both practical and efficient. As indicated by John Conde, you should normalize your database so that it looks like this:

ID  VALUE
1   123
1   123
1   678
2   23
2   24
2   25

Then you can easily prevent the situation from arising by disallowing duplicate rows (eg., by defining a UNIQUE index on the ID and VALUE columns). And if you can't / don't want to prevent it from happening, you can at least detect it much easier in pure SQL.

As it currently stands, I would get all rows into a PHP array and detect duplicate values from there. This isn't really efficient either, but at least it'll be more practical than trying to reach this in pure SQL.

$result = mysql_result('select ID, VALUE from table');
while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
     $values_array = explode( ',' , $row['VALUE'] );
     if(count($values_array) != count(array_unique($values_array)))
     {
          // There must be duplicate values in the array
          echo 'Row with id ' .$row['ID'] .' has duplicate values';
     }
}

You can do it like this, but normalizing your database would be much better. (Oh, and don't use these mysql_... functions; you should use mysqli or PDO. This is just to show you how it works).

Answer 2

Assuming you just have just 3 values comma separated . then you can use this via mysql and get rid of php:

  select a.id,a.v1 , a.v2,a.v3,a.value from (
       SELECT id,value,SUBSTRING_INDEX(value, ',', 1)as v1,
              SUBSTRING_INDEX(SUBSTRING_INDEX(value, ',', 2), ',', -1) as v2,
              SUBSTRING_INDEX(value, ',', -1) as v3
       FROM table1 )a
 where v1 = v2 or v1 = v3 or v2 = v3

DEMO HERE

OTHER DEMO with extra values

OBS: have make extra v1 ,v2,v3 separated ,so you may use them in your work