create function f_split(@c varchar(2000),@split varchar(2))
returns @t table(col VARCHAR(500))
as
begin
while(charindex(@split,@c)<>0)
begin
insert @t(col) values (substring(@c,1,charindex(@split,@c)-1))
set @c = stuff(@c,1,charindex(@split,@c),'')
end
insert @t(col) values (@c)
return
end
go
上面使用了表值函数。这个函数就可以当成一个表来查询,具体调用方法:
select * from dbo.f_split('31002/31007/31008','/')
select a.EquipmentNo
,(SELECT e.Name+',' FROM Employee e WHERE e.CodeNo IN(
select * from dbo.f_split(a.EmployeeNo,'/')
) FOR XML PATH('')) AS EmployeeName
FROM ProductWorkProcess a