Dharma Dharma - 1 month ago 6
MySQL Question

Need help in developing a mysql view that would split the comma seperated row value

Am trying to find a method to develop a view that would split 'col1' data based on comma. Example format below,

Master table,

col1 | col2 | col3

SN,MD,DE | T | 0

AF,SU | F | 1


View should look like below,

col1 | col2 | col3

SN | T | 0

MD | T | 0

DE | T | 0

AF | F | 1

SU | F | 1


Tried to build it using function, functions works well in splitting it & dumping the required rows on a temp table. But am not able to successfully incorporate that function into "create view SQL". Any suggestions would be helpful. Am pasting the body section of the function alone,

create function rpt(string, string, int)

.....

SET retval = '';

SET integ = inte;

SET i = 0;

SET num = LENGTH(stri)-LENGTH(REPLACE(stri,deli,''));

IF LENGTH(stri) = 0 THEN

RETURN '';

END IF;

WHILE ( i <= num ) DO

IF num = 0 THEN

SET retval = stri;

insert into temprpt values(integ,retval,flg);

ELSE

SET retval = book01d.split(i, deli, stri);

insert into temprpt values(integ,retval,flg);

END IF;

SET i = i + 1;

END WHILE;

RETURN v;

END


The split function inside this function would help splitting the comma separated values. So on a final note, am able to split the values & dump it into temprpt table. Now we need to call this function & at the same time read from the temp table as well.

Thanks,
Dharmaraj.G

Answer

you can directly access this without using view

SELECT master_tbl.col3,master_tbl.col2
SUBSTRING_INDEX(SUBSTRING_INDEX(master_tbl.col2, ',', numbers.n), ',', -1)       
col1 FROM
(SELECT 1 n UNION ALL
SELECT 2 UNION ALL SELECT 3 UNION ALL
SELECT 4 UNION ALL SELECT 5) numbers INNER JOIN master_tbl
ON CHAR_LENGTH(master_bl.col1)
-CHAR_LENGTH(REPLACE(master_tbl.col1, ',', ''))>=numbers.n-1
ORDER BY col3, n;

Here In ON condition we are just join our master_tbl table to derived table numbers so only those who have this condition true joined.
Note :- This only work under 5 comma if you want for more comma you have to ad more union select.