SQL Server 2005 自定义split 函数

自定义split函数，根据你传入的字符串和分割字符串标示。返回截取后的字符串table；

 

create function fun_split (

    @str varchar(8000) --要分拆的字符串

    ,@split varchar(10) --字符串分隔符

)

returns @returnTable table(string varchar(8000))

as

begin

    declare @i int

    declare @len int

    select @len = len(@split), @i= charindex(@split, @str)

    while @i > 0

    begin

       insert into @returnTable values(left(@str, @i - 1))

       select @str = substring(@str, @i + @len, 8000)

       select @i = charindex(@split, @str)

    end

    if @str <> '' insert into @returnTable values(@str)

    return

end

 

select * from fun_split('1,2,3,4,5,6,7,8', ',')

 

结果：

string
---------
1
2
3
4
5
6
7
8