0

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?

user0293
  • 1
  • 1
  • 1
  • 2
  • split field into individual values, insert individual values. – Marc B Dec 10 '15 at 20:41
  • not sure, how can we achieve this, table2variables can hold any numbers of values with comma separator. – user0293 Dec 10 '15 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 Dec 10 '15 at 20:45
  • could show me an example ? – user0293 Dec 10 '15 at 20:47
  • @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 Dec 10 '15 at 21:35
  • I know, I deleted my comment. – Barmar Dec 10 '15 at 21:36

1 Answers1

-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
somaredi
  • 9
  • 2