sql split comma separated string into rows

1CREATE FUNCTION Split
2(
3  @delimited nvarchar(max),
4  @delimiter nvarchar(100)
5) RETURNS @t TABLE
6(
7-- Id column can be commented out, not required for sql splitting string
8  id int identity(1,1), -- I use this column for numbering splitted parts
9  val nvarchar(max)
10)
11AS
12BEGIN
13  declare @xml xml
14  set @xml = N'<root><r>' + replace(@delimited,@delimiter,'</r><r>') + '</r></root>'
15
16  insert into @t(val)
17  select
18    r.value('.','varchar(max)') as item
19  from @xml.nodes('//root/r') as records(r)
20
21  RETURN
22END
23GO

1sql split string