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


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

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

I only want to return row 1 in this instance?




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:

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




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

  select,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

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

People are also looking for solutions to the problem: php - Multiple delete through checkout


Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.