实现split功能的函数

/*
功能：实现split功能的函数
*/

create function dbo.fn_split
(
@inputstr varchar ( 8000 ),
@seprator varchar ( 10 )
)
returns @temp table (a varchar ( 200 ))
as

begin
declare @i int

set @inputstr = rtrim ( ltrim ( @inputstr ))
set @i = charindex ( @seprator , @inputstr )

while @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

if @inputstr <> ' / '
insert @temp values ( @inputstr )

return
end
go

-- 调用

declare @s varchar ( 1000 )

set @s = ' 1,2,43,5,4,3 '

select a pid,hid = 3 from dbo.fn_split( @s , ' , ' )

drop function dbo.fn_split

/*
pid     hid
------- -----
1       3
2       3
43      3
5       3
4       3
3       3

（所影响的行数为 6 行）

*/