SQL: Split comma separated string list with a query?

Question

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 since I have no clue how to use functions and would love to keep it all simple.

Here is what I already found:

SELECT id, 
Substring(PaymentConditions, 1, Charindex(',', PaymentConditions)-1) as COND_1,
Substring(PaymentConditions, Charindex(',', PaymentConditions)+1, LEN(ANGEBOT.STDTXT)) as  COND_2
from Payment
WHERE id = '1'

But this only outputs

id    COND_1   COND_2
1     ZBE1     AP1, LST2, CC1

Is there a way to split everything from PaymentConditions to COND_1, COND_2, COND_3 and so on?

Thanks in advance.

take a look at http://stackoverflow.com/questions/5928599/equivalent-of-explode-to-work-with-strings-in-mysql — DevTheJo, Apr 13 '17 at 08:08
you could use `STRING_SPLIT` https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql — Jodrell, Apr 13 '17 at 08:59
Am I right in understanding that in your output you want as many columns as there are payment conditions and this to happen dynamically i.e. say for the first row output as - 1|ZBE1|AP1|LST2|CC1? — VKarthik, Jul 20 '17 at 23:47

score 2 · Answer 1 · edited Mar 13 '18 at 03:45

first create function to split values

create function [dbo].[udf_splitstring] (@tokens    varchar(max),
                                   @delimiter varchar(5))
returns @split table (
  token varchar(200) not null )
as



  begin

      declare @list xml

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

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

      return

  end  


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

    INSERT INTO #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

score 1 · Accepted Answer · answered Jul 20 '17 at 22:23

1

declare @SchoolYearList nvarchar(max)='2014,2015,2016'
declare @start int=1
declare @length int=4
create table #TempFY(SchoolYear int)
while @start<len(@SchoolYearList)
BEGIN

Insert into #TempFY
select SUBSTRING(@SchoolYearList,@start,@length)
set @start=@start+5
END
Select SchoolYear from #TempFY

answered Jul 20 '17 at 22:23

Ridhima V

26
2

3

It would be nice if you add comments on this awnser. – Felipe Oriani Jul 20 '17 at 22:26

score 0 · Answer 3 · edited Apr 11 '20 at 01:12

0

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

edited Apr 11 '20 at 01:12

Weihui Guo

3,669
5
34
56

answered Jan 30 '18 at 02:25

Xin

33,823
14
84
85

SQL: Split comma separated string list with a query?

3 Answers3

Linked