CODEWITHSUNDEEP
mysql
Marco Disco
Marco Disco

Reputation: 565

MySQL - How to split values in single strings using comma

I have MySql (8.0) with two tables, main:

 ID | TITLE | kindof
 1  |  aaa  |  shop
 2  |  bbb  |  food 
 3  |  ccc  |  market

category:

 ID | TITLE | CATEGORY
 1  |  aaa  |  design, home, clothing
 2  |  bbb  |  asian, indian 
 3  |  ccc  |  second hand

my node/express (main ID is auto increment):

let sql = BEGIN; INSERT INTO main (title,kindof) VALUES("${[req.body.title]}","${req.body.kindof}); INSERT INTO categories (id,title,category) VALUES(LAST_INSERT_ID(),"${[req.body.title]}"," ${[req.body.categories]}"); COMMIT;;

I would like to have:

 ID | TITLE | CATEGORY
 1  |  aaa  |  design
 1  |  aaa  |  home
 1  |  aaa  |  clothing
 2  |  bbb  |  asian
 2  |  bbb  |  indian
 3  |  ccc  |  second hand

req.body.category looks like that: {design, home, clothing}

How can I split the string using the comma in MySQL? Thanks

Upvotes: 0

Views: 276

Answers (2)

Akina
Akina

Reputation: 42632

In MySQL 8+ you may use, for example,

SELECT category.ID, category.TITLE, TRIM(jsontable.value) CATEGORY
FROM category
CROSS JOIN JSON_TABLE( CONCAT('["', REPLACE(category.CATEGORY, ',', '","'), '"]'),
                       "$[*]" COLUMNS( value VARCHAR(254) PATH "$" )
                     ) AS jsontable
ORDER BY category.ID, jsontable.value;

fiddle

Upvotes: 2

Strawberry
Strawberry

Reputation: 33945

Consider the following. I have table (ints) of integers (i) from 0 to 9...

DROP TABLE IF EXISTS ints;

CREATE TABLE ints(i INT NOT NULL PRIMARY KEY);
INSERT INTO ints VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

DROP TABLE IF EXISTS category;

CREATE TABLE category
(id INT NOT NULL
,title VARCHAR(12) NOT NULL
,category VARCHAR(100) NOT NULL
);

INSERT INTO category VALUES
(1,'aaa','design, home, clothing'),
(2,'bbb','asian, indian'),
(3,'ccc','second hand');

DROP TABLE IF EXISTS main_category;

CREATE TABLE main_category
AS
SELECT DISTINCT id 
              , TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(category,',',i+1),',',-1)) category
           FROM category 
              , ints;

DROP TABLE IF EXISTS category;


ALTER TABLE main_category ADD PRIMARY KEY(id,category);

SELECT * FROM main_category;
+----+-------------+
| id | category    |
+----+-------------+
|  1 | design      |
|  2 | asian       |
|  3 | second hand |
|  1 | home        |
|  2 | indian      |
|  1 | clothing    |
+----+-------------+

Upvotes: 0

Related Questions