CODEWITHSUNDEEP
mysqlsqlselect
Primoz Rome
Primoz Rome

Reputation: 11031

MySql get list of unique words from table where values in a field separated with comma

I am not sure if this is possible with 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 a all unique words from tags field, to get out something like this:

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

Upvotes: 4

Views: 2927

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269553

You can do this in SQL, although it is not pretty.

select distinct reverse(substring_index(reverse(substring_index(tags, ',', n.n)), ',', 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 need to be sure that the subquery n has at least the number of words in each tags.

Here is the SQLFiddle that demonstrates this.

This is cross joining the original data with sequential numbers. It then picks out the nth value from the tags strings, using substring_index().

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

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

Upvotes: 6

Rafael
Rafael

Reputation: 2817

The only way i can think of doing this in the database is with a stored procedure, this stored procedure will iterate over every row, extract and analyze its contents, althogh, it won't be very efficient.

Upvotes: 0

Related Questions