sql中分列问题

来源：百度知道 编辑：UC知道 时间：2024/05/29 18:23:25
sql中某一列中长度不等并用逗号隔开的数据，如：第一条是2536，417，4471 第二条是6632，25 怎样把它们分开变成若干列

id 代表要分列的列
思想：先求出'，'的个数——〉(len(id)-len(replace(id,',','')))
在使用substring进行分割
有点麻烦，逗号再多的时候，后面还要继续判断，仅此举例，敬请收下！
select case when(len(id)-len(replace(id,',',''))) = 0 then substring(id,1,len(id))
when(len(id)-len(replace(id,',',''))) <> 0 then substring(id,1,CHARINDEX(',',id)-1)
end as a,
case when (len(id)-len(replace(id,',',''))) = 1 then substring(id,CHARINDEX(',',id)+1,len(id)-CHARINDEX(',',id))
when (len(id)-len(replace(id,',',''))) = 2 then
substring((substring(id,CHARINDEX(',',id)+1,len(id)-CHARINDEX(',',id))),1,CHARINDEX(',',(substring(id,CHARINDEX(',',id)+1,len(id)-CHARINDEX(',',id))))-1)
end as b,
case when (len(id)-len(replace(id,',',''))) = 2 then substring((substring(id,CHARINDEX(',',id)+1,len(id)-CHARINDEX(',',id))),CHARIND