mysql 逗号分隔变多行_MySQL逗号分隔的值分成多行

I'm trying to split string values, which is passed as the parameter to my SQL function into multiple rows. I tried many samples out there but I'm not able to find the correct output.

This is my code Here:

DROP function if exists F_Split;

DROP TABLE if exists temp1;

CREATE TEMPORARY TABLE temp1(val CHAR(255));

DELIMITER $$

CREATE FUNCTION F_Split (x1 char(50))

RETURNS char(100)

DETERMINISTIC

BEGIN

DECLARE colval varchar(100);

SET @sql = CONCAT("insert into temp1 (val) values ('", REPLACE(x1, ",", "'),('"),"');");

select val from temp1 into colval;

RETURN colval;

END$$

DELIMITER ;

select F_Split('6,4');

I'm calling F_Split('6,4') with a single parameter, I'm trying to store the values into a temp table and function needs to return 2 rows based on the comma separation. i.e

Excepted Output:

Row1:6

Row2:4

Actual Output:

Null.

I know the insert statement is not working properly but I'm not sure how to fix this.

I want to Implement in the User Defined Function.

Help needed! Thanks

解决方案

you can also use a query like this. The column id is only for testing. If you want split more values than 4 you must modify the UNION and also the CONCAT string ',,,,'

SELECT

id

, SUBSTRING_INDEX( SUBSTRING_INDEX(val, ',', id), ',', -1) AS myvalue

FROM (

SELECT 1 AS id UNION SELECT 2 UNION SELECT 3 UNION SELECT 4

) cnt

CROSS JOIN (SELECT CONCAT('123,456,6678',',,,,') AS val) AS c

HAVING myvalue <> '';

sample

mysql> SELECT

-> id

-> , SUBSTRING_INDEX( SUBSTRING_INDEX(val, ',', id), ',', -1) AS myvalue

-> FROM (

-> SELECT 1 AS id UNION SELECT 2 UNION SELECT 3 UNION SELECT 4

-> ) cnt

-> CROSS JOIN (SELECT CONCAT('123,456,6678',',,,,') AS val) AS c

-> HAVING myvalue <> '';

+----+---------+

| id | myvalue |

+----+---------+

| 1 | 123 |

| 2 | 456 |

| 3 | 6678 |

+----+---------+

3 rows in set (0,00 sec)

mysql>