0

I am fairly new to SQL Programming and I can't find a solution to a problem I'm having.

I have a table which looks like that

ID     Var_to_split
---------------------
1      abc, def, ghi
2      jkl
3      mno, pqr

the output should be

ID  Var
--------
1   abc
1   def
1   ghi
2   jkl
3   mno
3   pqr

I don't know how many characters there are in each row I just know that they are delimited by a ','

I found several questions on here to split a String. (eg T-SQL: Opposite to string concatenation - how to split string into multiple records)

But they all work only with a single string. I want it to work within a table for each ID.

Community
  • 1
  • 1
Johanna
  • 15
  • 5
  • possible duplicate of [is there a "split" function in t-sql for a SELECT query](http://stackoverflow.com/questions/21165414/is-there-a-split-function-in-t-sql-for-a-select-query) – bummi Oct 17 '14 at 13:29
  • 2
    Combine the answers you found with [`Table-Valued User-Defined Functions`](http://technet.microsoft.com/en-us/library/ms191165(v=sql.105).aspx) and [`Using APPLY`](http://technet.microsoft.com/en-us/library/ms175156(v=sql.105).aspx) – Pred Oct 17 '14 at 13:49
  • 1
    http://sqlperformance.com/2012/07/t-sql-queries/split-strings – Aaron Bertrand Oct 17 '14 at 15:30
  • 1
    This is a duplicate of so many questions, I had to pick one to close the question, but none provide a better answer than @AaronBertrand's article on the subject, so although I am voting to close the question as a duplicate, my real advice is read, and probably re-read the article. It should answer all the questions you have. – GarethD Oct 17 '14 at 16:21
  • 1
    Thanks @GarethD. And next read this - because really you shouldn't be storing the data in the database that way, and if you are building that string at insert time, there are better ways all around. http://sqlperformance.com/2012/08/t-sql-queries/splitting-strings-now-with-less-t-sql – Aaron Bertrand Oct 17 '14 at 16:24

0 Answers0