MySQL distinct string

Question

I have the following sample table.

drop table if exists fruit;
create table fruit
 (
 id int not null auto_increment primary key,
 tag varchar(100) not null
 );
insert into fruit (tag) values
('apples, oranges, apples'),('apples, pears, pears'),('figs, oranges, figs, oranges');

So the table looks like

> select tag from fruit;
+--------------------------------+
| tag |
+--------------------------------+
| apples, oranges, apples |
| apples, pears, pears |
| figs, oranges, figs, oranges |
+-----------------+
3 rows in set (0.00 sec)

How can I make the rows have unique fruits? For example, I would like to have the following resulting table.

 tag 
| apples, oranges | 
| apples, pears | 
| figs, oranges | 

Any suggestions would be greatly appreciated.

• Vérace: You appear to have implemented a SET datatype (an abomination - repeating groups). I'd redesign if I were you! You can't do want you want in SQL.

Answer 1

You came to the right place !!! Hang onto something because you are going to roll on the floor laughing

I answered a similar question just like this back on April 22, 2014 involving apples, oranges, pears, and figs: MySQL group concat not showing distinct values when joined to another table

In your case, you need to assemble distinct fruits by the id it belongs to

Ready for a convoluted solution tailor made for you ?

PROPOSED QUERY

SELECT CONCAT('SELECT id,REPLACE(GROUP_CONCAT(DISTINCT frt),'','','', '') tag FROM (',
CONCAT('SELECT ',GROUP_CONCAT(fruitstr SEPARATOR ' UNION SELECT ')),') A GROUP BY id') 
INTO @TagSQL FROM
(
 SELECT REPLACE(CONCAT(REPLACE(QUOTE(tag),', ',CONCAT(''',',
 id,' id UNION SELECT ''')),',',id,' id'),',',' frt,') fruitstr FROM fruit
) A;
SELECT @TagSQL\G
PREPARE s FROM @TagSQL; EXECUTE s; DEALLOCATE PREPARE s;

YOUR SAMPLE DATA

mysql> drop database if exists dinesh;
Query OK, 2 rows affected (0.38 sec)
mysql> create database dinesh;
Query OK, 1 row affected (0.01 sec)
mysql> use dinesh
Database changed
mysql> create table fruit
 -> (
 -> id int not null auto_increment primary key,
 -> tag varchar(100) not null
 -> );
Query OK, 0 rows affected (0.28 sec)
mysql> insert into fruit (tag) values
 -> ('apples, oranges, apples'),
 -> ('apples, pears, pears'),
 -> ('figs, oranges, figs, oranges');
Query OK, 3 rows affected (0.03 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from fruit;
+----+------------------------------+
| id | tag |
+----+------------------------------+
| 1 | apples, oranges, apples |
| 2 | apples, pears, pears |
| 3 | figs, oranges, figs, oranges |
+----+------------------------------+
3 rows in set (0.00 sec)
mysql>

PROPOSED QUERY EXECUTED

mysql> SELECT CONCAT('SELECT id,REPLACE(GROUP_CONCAT(DISTINCT frt),'','','', '') tag FROM (',
 -> CONCAT('SELECT ',GROUP_CONCAT(fruitstr SEPARATOR ' UNION SELECT ')),') A GROUP BY id')
 -> INTO @TagSQL FROM
 -> (
 -> SELECT REPLACE(CONCAT(REPLACE(QUOTE(tag),', ',CONCAT(''',',
 -> id,' id UNION SELECT ''')),',',id,' id'),',',' frt,') fruitstr FROM fruit
 -> ) A;
Query OK, 1 row affected (0.03 sec)
mysql> SELECT @TagSQL\G
*************************** 1. row ***************************
@TagSQL: SELECT id,REPLACE(GROUP_CONCAT(DISTINCT frt),',',', ') tag FROM (SELECT 'apples' frt,1 id UNION SELECT 'oranges' frt,1 id UNION SELECT 'apples' frt,1 id UNION SELECT 'apples' frt,2 id UNION SELECT 'pears' frt,2 id UNION SELECT 'pears' frt,2 id UNION SELECT 'figs' frt,3 id UNION SELECT 'oranges' frt,3 id UNION SELECT 'figs' frt,3 id UNION SELECT 'oranges' frt,3 id) A GROUP BY id
1 row in set (0.00 sec)
mysql> PREPARE s FROM @TagSQL; EXECUTE s; DEALLOCATE PREPARE s;
Query OK, 0 rows affected (0.00 sec)
Statement prepared
+----+-----------------+
| id | tag |
+----+-----------------+
| 1 | apples, oranges |
| 2 | apples, pears |
| 3 | figs, oranges |
+----+-----------------+
3 rows in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql>

GIVE IT A TRY !!!

P.S. Like I said in my earlier post,

I TOLD YOU IT WAS CONVOLUTED !!!

• Dinesh: Can you help with this question too please - dba.stackexchange.com/questions/94813/…