SQL：用查询分割逗号分隔的字符串列表？ [英] SQL: Split comma separated string list with a query?

问题描述

这是我的表结构：

id PaymentCond
1  ZBE1, AP1, LST2, CC1
2  VB3, CC1, ZBE1

我需要拆分列 PaymentCond ，并希望通过简单的sql查询来做到这一点，因为我不知道如何使用函数，并且希望使它们保持简单。

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.

在这里我已经找到了：

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'

但这仅输出

id    COND_1   COND_2
1     ZBE1     AP1, LST2, CC1

是否可以将所有内容从 PaymentConditions 拆分为 COND_1 ， COND_2 ， COND_3 等？

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

谢谢

推荐答案

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

这篇关于SQL：用查询分割逗号分隔的字符串列表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！