0

I have a string variable which is received as input to a stored procedure since MySQL doesn't support arrays.

arr='ra,ba,ca,da'

and I wanted to convert that into table of values

Table
-----
 ra
 ba
 ca
 da

What would be the simplest select statement to achieve that?

Improve this question
8
  • Is there a maximum number of items in the string? If not, you need to write a stored procedure to do it. If there is, you can use UNION of queries that use SUBSTRING_INDEX to select each item up to the maximum. Commented Sep 13, 2017 at 23:13
  • @barmar I am looking for a simple SQL statement that can achieve this task, can you please share the query? I tried no. of options to do so but did not succeed. Commented Sep 14, 2017 at 4:07
  • Please edit your question to show what you tried. If it's really true that none of the ideas in the linked question work, I'll reopen and we can try helping you. Commented Sep 14, 2017 at 15:03
  • Note that it won't be a "simple SQL statement". There's nothing built into SQL that does this, you have to write code. Is there a reason you have to do it in SQL rather than a client application language? Most programming languages have simple functions or library routines to split a comma-separated string. Commented Sep 14, 2017 at 15:04
  • @barmar The linked question that you added is reading out of a table and exploding out into a new table. In my case, the input is stored as a variable, and how to convert that into a table was a challenge. I wanted to do this since MySQL doesn't support anything like array data type, so the input to the stored procedure will be an array which is actually a string separated by a comma, and then I actually wanted to convert each value into a separate row in a table to perform the further action over it. Commented Sep 14, 2017 at 17:57
Related questions
1869
Insert results of a stored procedure into a temporary table
1500
Can I concatenate multiple MySQL rows into one field?
471
SQL Server SELECT into existing table
Load 7 more related questions Show fewer related questions

0

Reset to default

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.