php - Detect duplicate values in a comma separated field in mysql

Solution:

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
Copy code

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';
     }
}
Copy code

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).

Solution:

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
Copy code