0

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

Improve this question
2
  • Specify MySQL version. Commented Jun 29, 2020 at 11:25
  • version 8.0 at least is written in phpmyadmin Commented Jun 29, 2020 at 11:30

2 Answers 2

Reset to default
2

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

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

4 Comments

thanks. your solution looks smoother. basicaly you trim them afterwards right?
your solution works perfectly! Thanks! what I don't understand is: is this a "filter" for when I want to get the categories?
@MarcoDisco I don't understand your question. This code unpivots your CSV data only. Put it into CTE (removing ORDER BY, of course), and then use obtained data as you need. As a filter, as a data table for joining, etc.
@MarcoDisco For your MySQL version you may also use the solution provided by Strawberry, after replacing static numbers table (ints) with recursive CTE.
0

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    |
+----+-------------+
Improve this answer

6 Comments

thanks! So if I understood: you want to create a new table main_category where insert the trimmed values?
Well, no. If it was me, I'd have table of 'mains', a table of 'categories', and a table which records which 'category' belongs to which 'main'. But you should be allowed to do whatever you want.
I meant that a table (main_category) that joins the two
Yes, but it would hold the id of the main, and the id of the category. My table is a stepping stone towards that, if you like.
sure, I want to keep that. Just the i+1 doesn't work. but let me try better
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.