MySql gets a list of unique words from a table where the values ​​in the field are separated by a comma

Question

I'm not sure if this is possible using pure SQL (MySQL), but I will ask anyway. I have a table like this:

ID TAGS ----------------------------- 1 word1,word2,word3 2 word2,word4 3 word3,word5,word6,word7 ... 

I would like to select all the unique words from the tag field to get something like this:

 TAGS ----- word1 word2 word3 word4 word5 word6 word7 

Answer 1

You can do this in SQL, although this is not very.

 select distinct reverse(substring_index(reverse(substring_index(tags, ',', nn)), ',', 1)) as word from t cross join (select 1 as n union all select 2 as n union all select 3 as n union all select 4 as n) n having word is not null 

You must be sure that the subquery n has at least the number of words in each tag.

Here is a SQLFiddle that demonstrates this.

This is a cross connecting the source data with sequential numbers. He then selects the nth value from the tag lines using substring_index() .

To get the maximum number of tags, you can do:

 select max(length(tags) - length(replace(tags, ',', 1))+1 from t 

Answer 2

The only way I can do this in the database is with a stored procedure, this stored procedure will iterate over each line, extract and parse its contents, but it will not be very efficient.