Sql: Split Comma Separated String List With A Query?

Here is my table structure: id PaymentCond 1 ZBE1, AP1, LST2, CC1 2 VB3, CC1, ZBE1 I need to split the column PaymentCond, and would love to do that with a simple sql query sinc

Solution 1:

first create function to split values

createfunction [dbo].[udf_splitstring] (@tokensvarchar(max),
                                   @delimitervarchar(5))
returns@splittable (
  token varchar(200) notnull )
asbegindeclare@list xml

      select@list=cast('<a>'+ replace(@tokens, @delimiter, '</a><a>')
                          +'</a>'as xml)

      insertinto@split
                  (token)
      select ltrim(t.value('.', 'varchar(200)')) as data
      from@list.nodes('/a') as x(t)

      returnendCREATETABLE #Table1
        ([id] int, [PaymentCond] varchar(20))
    ;

    INSERTINTO #Table1
        ([id], [PaymentCond])
    VALUES
        (1, 'ZBE1, AP1, LST2, CC1'),
        (2, 'VB3, CC1, ZBE1')
    ;
    select id, token FROM #Table1 as t1
    CROSS APPLY [dbo].UDF_SPLITSTRING([PaymentCond],',') as t2

output

id  token
1   ZBE1
1   AP1
1   LST2
1   CC1
2   VB3
2   CC1
2   ZBE1

Solution 2:

declare@SchoolYearList nvarchar(max)='2014,2015,2016'declare@startint=1declare@lengthint=4createtable #TempFY(SchoolYear int)
while @start<len(@SchoolYearList)
BEGINInsertinto #TempFY
selectSUBSTRING(@SchoolYearList,@start,@length)
set@start=@start+5ENDSelect SchoolYear from #TempFY

Solution 3:

There is a new table-valued function in SQL Server STRING_SPLIT:

DECLARE@tags NVARCHAR(400) ='aaaa,bbb,,cc,d'SELECT*FROM STRING_SPLIT(@tags, ',')  

You will get:

But be careful its availability in your DB: The STRING_SPLIT function is available only under compatibility level 130