how to insert values from comma separator in SQL? [duplicate]

Question

in sql store procedure i have variable

 IN `table2variables` VARCHAR(5000),

which hold values like this : table2variables ="88,61,35"

now i want to insert those values in table like this :

INSERT into table2(id, val) values ([id],"88");
INSERT into table2(id, val) values ([id],"61");
INSERT into table2(id, val) values ([id],"35");

so question is how can i get values from that variable? and write only one sentence to INSERT?

split field into individual values, insert individual values. — Marc B
– Marc B, Commented Dec 10, 2015 at 20:41
not sure, how can we achieve this, table2variables can hold any numbers of values with comma separator. — user0293
– user0293, Commented Dec 10, 2015 at 20:45
If the number of values within the field is fixed, then using substring_index() will help you. If this number is dynamic, then you have to write a stored procedure to do it in mysql only. May be easier to implement in a traditional programming language. — Shadow
– Shadow, Commented Dec 10, 2015 at 20:45
@Barmar Thanks for comment,, i am not inserting "88,61,35" to one row, i want to insert each number to row.( here it will be 3 rows), is that create an issue? — user0293
– user0293, Commented Dec 10, 2015 at 21:35

somaredi · Accepted Answer · 2015-12-10 21:01:01Z

-1

You can use backslash(\) to insert commas into values. The backslash acts like an escape character. The concept is very much similar to many programming languages.

INSERT into table2(id, val) values ([id],"88\,61\,35");

To update:

UPDATE table2 
SET val = "50\,60\,90"
WHERE id = 999

edited Dec 10, 2015 at 21:01

answered Dec 10, 2015 at 20:51

somaredi

92 bronze badges

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

5 Comments

user0293 Over a year ago

and what if i want to update that records ?

somaredi Over a year ago

I updated my answer @user0293

user0293 Over a year ago

is any other way to replace string ="88,61,35" to "88\,61\,35" , in javascript,php or sql?

Barmar Over a year ago

Commas don't need to be escaped inside strings.

Barmar Over a year ago

What does this answer have to do with the question. He doesn't want a comma-separated list in a single column. He wants to split the string in the stored procedure, and put each element in a separate row.

Collectives™ on Stack Overflow

how to insert values from comma separator in SQL? [duplicate]

1 Answer 1

5 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Linked

Related