Question 21464 in Mysql Question posted in
The official MySQL documentation can be found here.

How to extract all different tags from one column? – Mysql

Considering the data-table as below:

tags
alpha
alpha, beta
delta
beta, gamma

How can get all different tags in one query? So the expected result should be:

alpha
beta
gamma
delta

(order is not important)

I have been thinking a lot, but could not find out the solution. 🙂

Tags:

2

Answers


  1. 0

    Assuming that there would be a max of 3 CSV words per record and that you are using MySQL 8+, we can try using a regex substring approach:

    SELECT DISTINCT tags
FROM
(
    SELECT REGEXP_SUBSTR(tags, '\w+', 1, 1) AS tags FROM yourTable
    UNION ALL
    SELECT REGEXP_SUBSTR(tags, '\w+', 1, 2) FROM yourTable
    UNION ALL
    SELECT REGEXP_SUBSTR(tags, '\w+', 1, 3) FROM yourTable
) t;
    Login or Signup to reply.
  2. 0

    We get all tags splited by ‘,’ then we use union to keeps unique records only :

    select TRIM(SUBSTRING_INDEX(tags, ',', 1))
from tags
union 
select TRIM(SUBSTRING_INDEX(tags, ',', -1))
from tags
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search