SQL 拆分字符串(with写法)

create table table1(Name varchar(50), Grade int)
insert into table1 values(‘张三,李四,王五’, 1)
insert into table1 values(‘赵六,孙七,周八’, 2)
insert into table1 values(‘吴九’, 3)
–必须以’,'结尾，否则无法截取最后一个名字
update table1 set Name = Name + ‘,’

with t as(
select Name, CHARINDEX(’,’, Name) as StartLocation, CHARINDEX(’,’, Name) - 1 as EndLength, Grade
from table1
union all
select Name, CHARINDEX(’,’, Name, StartLocation + 1) as StartLocation,
CHARINDEX(’,’, Name, StartLocation + 1) - StartLocation - 1 as EndLength, Grade
from t
where StartLocation <> 0
)
select Name as OldName, (SUBSTRING(Name, StartLocation - EndLength, EndLength)) Name,
Grade, StartLocation, EndLength
from t
where StartLocation <> 0
order by Grade

drop table table1

运行后结果：