CODEWITHSUNDEEP
mysqlstring
55SK55
55SK55

Reputation: 691

Comma separated string into Table in MySql

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?

Upvotes: 0

Views: 1081

Answers (2)

Ibrahim Mohamed
Ibrahim Mohamed

Reputation: 433

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

Upvotes: 1

PSK
PSK

Reputation: 17943

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

Upvotes: 1

Related Questions