一、程序片段：
declare @tempstr varchar(100)
set @tempstr=’1,3,4,5,447,8,10′
declare @tempi int
select @tempstr=@tempstr+’,’
select @tempi=charindex(‘,’,@tempstr)
while @tempi<>0
begin
print left(@tempstr,@tempi-1)
select @tempstr=substring(@tempstr,@tempi+1,len(@tempstr) – @tempi)
select @tempi=charindex(‘,’,@tempstr)
end
select @tempi=charindex(‘,’,@tempstr)
while @tempi<>0
begin
print left(@tempstr,@tempi-1)
select @tempstr=substring(@tempstr,@tempi+1,len(@tempstr) – @tempi)
select @tempi=charindex(‘,’,@tempstr)
end
二、函数：
/*
功能：实现split功能的函数
*/
create function fn_split
(
@inputstr varchar(8000),
@seprator varchar(10)
)
returns @temp table (a varchar(200))
as
(
@inputstr varchar(8000),
@seprator varchar(10)
)
returns @temp table (a varchar(200))
as
begin
declare @i int
declare @i int
set @inputstr = rtrim(ltrim(@inputstr))
set @i = charindex(@seprator, @inputstr)
set @i = charindex(@seprator, @inputstr)
while @i >= 1
begin
insert @temp values(left(@inputstr, @i – 1))
begin
insert @temp values(left(@inputstr, @i – 1))
set @inputstr = substring(@inputstr, @i + 1, len(@inputstr) – @i)
set @i = charindex(@seprator, @inputstr)
end
set @i = charindex(@seprator, @inputstr)
end
if @inputstr <> ‘/’
insert @temp values(@inputstr)
insert @temp values(@inputstr)
return
end
end
用法：select * from dbo.f_split(ABC:BC:C:D:E,:)