How to split string in SQL Server and store each one in new row

Question

I have a column that contains string of words split by comma I want to split each word to new row.

This is table example

PL          | Year
------------+------
Java, C#, C | 2018

I want results like this

PL     | Year
-------+--------
Java   | 2018
C#     | 2018
C++    | 2018

My query attempt:

select into #TempTable PL, Year 
from myTable;

[SQL Server split CSV into multiple rows](https://stackoverflow.com/questions/9811161/sql-server-split-csv-into-multiple-rows) — Lukasz Szozda, Mar 20 '18 at 20:10
Don't use that horrific splitter found in the link above. If you have a splitter that has a while loop or cursor you should run away quickly. There are several far better options here. http://sqlperformance.com/2012/07/t-sql-queries/split-strings — Sean Lange, Mar 20 '18 at 20:12
on a side note.. there are heaps of answers for the same question.. it would have come up as you were typing the questions! How about you do some research to find out rather than expect someone to spoon feed you the answer? — Harry, Mar 20 '18 at 20:16
I should be more clear. The link above from scsimon is excellent. It is the splitter from the comment from lad2025 that should be avoided. — Sean Lange, Mar 20 '18 at 20:26

score 2 · Accepted Answer · answered Mar 20 '18 at 20:13

2

Try this one: Best in class for pure SQL Unless you write a CLR, this one blows all others out of the water for performance

answered Mar 20 '18 at 20:13

user1443098

1

Don't forget `string_split()` in 2016! – Xedni Mar 20 '18 at 21:03
Unfortunately string_split() does not return an order column. It's fine where the order of items doesn't matter, useless otherwise – user1443098 Mar 22 '18 at 14:20

1 Answers1