1

Let's say i have a column for tags. This tags is separated by space.

|tags                  |
|apple banana melon    |
|apple banana pineapple|
|apple melon pineapple |

I would like to get all the word in column tags, separated it by space and remove the duplicate word. So i will get output like this:

|unique_tags|
|apple      |
|banana     |
|melon      |
|pineapple  |

How to do this in mysql?

GusDeCooL
  • 5,639
  • 17
  • 68
  • 102
  • Select and separate all the tags; put the results into a temporary table, then select distinct from the temporary table. – Mike P Oct 26 '15 at 15:52
  • 1
    See this http://stackoverflow.com/questions/19073500/sql-split-comma-separated-row - using _SELECT DISTINCT ..._ would eliminate duplicates – PaulF Oct 26 '15 at 16:00

1 Answers1

2

First of all you should better refactor your database schema.

But I see many questions like this and I prefer this solution.

If you agree you can adapt it to your case this way:

http://sqlfiddle.com/#!9/2fd5e/7

SELECT DISTINCT
  SUBSTRING_INDEX(SUBSTRING_INDEX(t1.tags, ' ', numbers.n), ' ', -1) tag
FROM
  numbers 
INNER JOIN t1
  ON CHAR_LENGTH(t1.tags)
     -CHAR_LENGTH(REPLACE(t1.tags, ' ', ''))>=numbers.n-1

Mention that you have to create numbers table with number of records and consecutive values till max number values you expect in your tags field list.

Community
  • 1
  • 1
Alex
  • 16,739
  • 1
  • 28
  • 51
  • this is more awesome, without additional table: http://sqlfiddle.com/#!2/a213e4/1 – GusDeCooL Oct 26 '15 at 16:48
  • really ?? `SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4` you like this solution? son if you will have 10 elements or 20? your query will looks terrible. I really prefer to create one simple table and keep query readable. But that is up to you :-) – Alex Oct 26 '15 at 16:53