Table structure:
1_column 2_column
-----------------------------
1 data 1; data 2
2 data 3
3 data 1; data 15; data 6
4 data 2; data 3; data 16
5 data 3
Query: SELECT DISTINCT 2_column FROM table ORDER BY 1_column
The output returned is: 4 rows.
data 1; data 2
data 3
data 1; data 15, data 6
data 2; data 3; data 16
Now I would like to explode the values of the column 2_column such as data 1, data 2, data 3 etc. and then SELECT DISTINCT among the exploded values. How to make this with performance in mind and with MySQL alone?
Edit: Expected result:
data 1
data 2
data 3
data 6
data 15
data 16
Exploded DISTINCT values. 6 rows.
Obviously, you need to normalise your data. But I guess you can use something like this as a step towards achieving that...
SELECT * FROM my_table;
+---------+-------------------------+
| column1 | column2 |
+---------+-------------------------+
| 1 | data 1; data 2 |
| 2 | data 3 |
| 3 | data 1; data 15; data 6 |
| 4 | data 2; data 3; data 16 |
| 5 | data 3 |
+---------+-------------------------+
SELECT * FROM ints;
+---+
| i |
+---+
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
+---+
SELECT DISTINCT TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(column2,';',i),';',-1)) x
FROM my_table
, ints i
HAVING x <> '';
+---------+
| x |
+---------+
| data 1 |
| data 3 |
| data 2 |
| data 15 |
| data 6 |
| data 16 |
+---------+
The trivial way is the following:
DELIMITER $$
DROP PROCEDURE IF EXISTS getresults$$
CREATE PROCEDURE getresults()
BEGIN
DECLARE ite INT;
SET @s="";
select MAX(LENGTH(2_column)-LENGTH(REPLACE(2_column, ";", "")))+1 max
FROM mytable INTO ite;
WHILE (ite>0) DO
SET @s=concat(@s,
"UNION SELECT ltrim(substring_index(
SUBSTRING_INDEX(2_column, ';', ",ite,"), ';', -1)) data
FROM mytable ");
SET ite:=ite-1;
END WHILE;
SET @s=mid(@s, 6);
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END $$
DELIMITER ;
call getresults();
+---------+
| data |
+---------+
| data 2 |
| data 3 |
| data 6 |
| data 16 |
| data 15 |
| data 1 |
+---------+
I do like Strawberry answer which is more simple than my previous stored procedure
If you assume that you wont have more "pseudo columns" than rows, you can use the next instructions, to bypass the limitation of creating an int table :
SET @a:=0;
SELECT DISTINCT TRIM(SUBSTRING_INDEX(
SUBSTRING_INDEX(2_column,';',a.nb),';',-1)) data
FROM mytable,
(SELECT @a:=@a+1 nb
FROM mytable
WHERE @a<
(SELECT MAX(LENGTH(m1.2_column)
-LENGTH(REPLACE(m1.2_column, ";", "")))+1 max
FROM mytable m1)) a;