How to remove duplicate comma separated value in a single column in MySQL

Question

SELECT id, country FROM my_records

I've got the above result from MySQL query and i want to remove duplicate ID from the result. Not with the help of PHP code but do with MySQL query. Is there any function or query to do the same.

Thanks

Answer 1

I stuck into the similar situation and found that MySql does not provide any predefined function to overcome this problem.

To overcome I created a UDF, Please have a look below on the defination and usage.

DROP FUNCTION IF EXISTS `get_unique_items`;
DELIMITER //
CREATE FUNCTION `get_unique_items`(str varchar(1000)) RETURNS varchar(1000) CHARSET utf8
BEGIN

        SET @String      = str;
        SET @Occurrences = LENGTH(@String) - LENGTH(REPLACE(@String, ',', ''));
        SET @ret='';
        myloop: WHILE (@Occurrences > 0)
        DO 
            SET @myValue = SUBSTRING_INDEX(@String, ',', 1);
            IF (TRIM(@myValue) != '') THEN
                IF((LENGTH(@ret) - LENGTH(REPLACE(@ret, @myValue, '')))=0) THEN
                        SELECT CONCAT(@ret,@myValue,',') INTO @ret;
                END if;
            END IF;
            SET @Occurrences = LENGTH(@String) - LENGTH(REPLACE(@String, ',', ''));
            IF (@occurrences = 0) THEN 
                LEAVE myloop; 
            END IF;
            SET @String = SUBSTRING(@String,LENGTH(SUBSTRING_INDEX(@String, ',', 1))+2);
        END WHILE;    
SET @ret=concat(substring(@ret,1,length(@ret)-1), '');
return @ret;

END //
DELIMITER ;

Sample usage:

SELECT get_unique_items('2,2,2,22,2,3,3,3,34,34,,54,5,45,,65,6,5,,67,6,,34,34,2,3,23,2,32,,3,2,,323') AS 'Items';

Result:

2,22,3,34,54,45,65,67,23,32,323

Hope this help!

Answer 2

This may helps you.

DELIMITER //

DROP FUNCTION IF EXISTS `find_duplicate_using_comma` //
CREATE FUNCTION `find_duplicate_using_comma` (in_str LONGTEXT) RETURNS LONGTEXT
DETERMINISTIC
NO SQL
BEGIN


DECLARE out_str LONGTEXT DEFAULT NULL; -- pending output
DECLARE next_str TEXT DEFAULT NULL;    -- next element under consideration

dedup:
LOOP

  IF CHAR_LENGTH(TRIM(in_str)) = 0 OR in_str IS NULL THEN
    LEAVE dedup; -- no more data to consider
  END IF;

  SET next_str = SUBSTRING_INDEX(in_str,',',1);                   -- find the next element
  SET in_str = SUBSTRING(in_str FROM (CHAR_LENGTH(next_str) + 1 + 1)); -- remove that element

  SET in_str = TRIM(in_str), next_str = TRIM(next_str); -- trim the new and the rest

  IF FIND_IN_SET(next_str,out_str) OR CHAR_LENGTH(next_str) = 0 THEN -- if empty or already found
    ITERATE dedup;
  END IF;

  SET out_str = CONCAT_WS(',',out_str,next_str); -- append the new to pending output 

END LOOP;

RETURN out_str;

END //

DELIMITER ;

Example :

SELECT find_duplicate_using_comma('6675,8661,8661,8661,8661,8661,8661,8661,8661,8661,8661,8661,8661,8661,8661,8661,8661,8661') AS data;

Result : 6675,8661

Answer 3

The other solutions given are case insensitive, If you want result to be case sensitive and NOT remove accented matches like 'o' and 'ö', and looking for exact and strict match, here is the code

If utf8mb4_bin does not work, try utf8_bin or other binary types.

DELIMITER //

DROP FUNCTION IF EXISTS `find_duplicate_using_comma` //
CREATE FUNCTION `find_duplicate_using_comma` (in_str LONGTEXT) RETURNS LONGTEXT
DETERMINISTIC
NO SQL
BEGIN


DECLARE out_str LONGTEXT DEFAULT NULL; -- pending output
DECLARE next_str TEXT DEFAULT NULL;    -- next element under consideration

dedup:
LOOP

  IF CHAR_LENGTH(TRIM(in_str)) = 0 OR in_str IS NULL THEN
    LEAVE dedup; -- no more data to consider
  END IF;

  SET next_str = SUBSTRING_INDEX(in_str,',',1);                   -- find the next element
  SET in_str = SUBSTRING(in_str FROM (CHAR_LENGTH(next_str) + 1 + 1)); -- remove that element

  SET in_str = TRIM(in_str), next_str = TRIM(next_str); -- trim the new and the rest

  IF FIND_IN_SET(next_str collate utf8mb4_bin,out_str collate utf8mb4_bin) OR CHAR_LENGTH(next_str) = 0 THEN -- if empty or already found
    ITERATE dedup;
  END IF;

  SET out_str = CONCAT_WS(',',out_str,next_str); -- append the new to pending output 

END LOOP;

RETURN out_str;

END //

DELIMITER ;