Comma separated string into Table in MySql

Question

I'm trying to convert a comma separated string into a table result using MySql. Basically, I'm a SQL Server user and I don't know how to achieve the same in MySql. Please help me on this.

I've a sting like as below, basically a string array holds different values.

'45,55,65,41'

I wanted to get this converted into a table result as below

+---------+-----------------+
|     id    |  value        |
+---------+-----------------+
|     1     |     45        |
+---------+-----------------+
|     2     |     55        |
+---------+-----------------+
|     3     |     65        |
+---------+-----------------+
|     4     |     41        |
+---------+-----------------+

Ho to do this?

How do you do this in sql server

Strawberry
– Strawberry

2018-04-15 09:14:48 +00:00
Commented Apr 15, 2018 at 9:14 — Strawberry
– Strawberry, Commented Apr 15, 2018 at 9:14

PSK · Accepted Answer · 2018-04-15 09:23:32Z

1

You can try like following.

select distinct
  n  as Id,
  SUBSTRING_INDEX(SUBSTRING_INDEX('45,55,65,41', ',', numbers.n), ',', -1) Value
from
  (select @rownum := @rownum + 1 as n
from YourTable  
cross join (select @rownum := 0) r
) numbers 
order by
   n

Output

+----+-------+
| ID | Value |
+----+-------+
| 1  | 45    |
+----+-------+
| 2  | 55    |
+----+-------+
| 3  | 65    |
+----+-------+
| 4  | 41    |
+----+-------+

DEMO

edited Apr 15, 2018 at 9:23

answered Apr 15, 2018 at 9:17

PSK

18k5 gold badges35 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ibrahim Mohamed · Accepted Answer · 2018-04-15 09:42:38Z

Here is a solution:

DROP TEMPORARY TABLE if exists `converted_values`;
CREATE TEMPORARY TABLE `converted_values`( `id` int(11), `value` char(45) );
ALTER TABLE `converted_values` ADD PRIMARY KEY (`id`);
ALTER TABLE `converted_values` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
SET @sqlvar = CONCAT("INSERT INTO converted_values (value) VALUES ('", REPLACE('45,55,65,41', ",", "'),('"),"');");
PREPARE insert_statement FROM @sqlvar;
EXECUTE insert_statement;
SELECT * FROM converted_values;

You can find the comma separated string on the 5th line inside the REPLACE function: '45,55,65,41'.

Here is an online working example: https://www.db-fiddle.com/f/rvMDyc5JBnNQrDJ21rnRNX/1

Result:

+----+-------+
| id | value |
+----+-------+
| 1  | 45    |
+----+-------+
| 2  | 55    |
+----+-------+
| 3  | 65    |
+----+-------+
| 4  | 41    |
+----+-------+

Notice: This is a temporary table which doesn't get saved to your database. Ofcourse if you want to save the result in a table in your database you can use this portion of the code: (and replace the table name converted_values with your table name)

SET @sqlvar = CONCAT("INSERT INTO converted_values (value) VALUES ('", REPLACE('45,55,65,41', ",", "'),('"),"');");
PREPARE insert_statement FROM @sqlvar;
EXECUTE insert_statement;
SELECT * FROM converted_values;

Here is an online example: https://www.db-fiddle.com/f/rvMDyc5JBnNQrDJ21rnRNX/0

Collectives™ on Stack Overflow

Comma separated string into Table in MySql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related