SQL SERVER分割字串

1、使用指定的字串分割，返回分割後元素的個數
create function Get_StrLength
(
@str varchar(1024),
@split varchar(10)
)
returns int
as
begin
declare @location int
declare @start int
declare @length int
set @str=ltrim(rtrim(@str))
set @location=charindex(@split,@str)
set @length=1
while @location<>0
begin
set @start=@location+1
set @location=charindex(@split,@str,@start)
set @length=@length+1
end
return @length
end
調用方法：select dbo.Get_StrLength('7,5,6,7,a,f,d',',')
 
2、按指定符號分割字串，返回分割後指定索引的第幾個元素，像數組一樣
 
create function Get_StrOfIndex
(
@str varchar(1024),
@split varchar(10),
@index int
)
returns varchar(1024)
as
begin
declare @location int
declare @start int
declare @next int
declare @seed int
set @str=ltrim(rtrim(@str))
set @start=1
set @next=1
set @seed=len(@split)
set @location=charindex(@split,@str)
while @location<>0 and @index>@next
begin
set @start=@location+@seed
set @location=charindex(@split,@str,@start)
set @next=@next+1
end
--說明：這兒存在兩種情況：1、字串不存在分隔字元號 2、字串中存在分隔字元號，跳出while迴圈後，@location為0，那預設為字串後邊有一個分隔字元號。

if @location=0
select @location=len(@str)+1
return substring(@str,@start,@location-@start)
end
調用方法：select dbo.Get_StrOfIndex('8,9,3,3,4,5',',',26)
3、結合上邊兩個函數，返回分割後的元素
create function f_splitstr
(
@SourceSql varchar(8000),
@strSeprate varchar(100)
)
returns @temp table (F1 varchar(100))
as
begin
declare @ch as varchar(100)
set @SourceSql=@SourceSql+@StrSeprate
while(@SourceSql<>'')
begin
set
@ch=left(@SourceSql,Charindex(',',@SourceSql,1)-1)
insert @temp values(@ch)
set @SourceSql=stuff(@SourceSql,1,charindex(',',@SourceSql,1),'')
end
return
end
調用方法：select * from f_splitstr('1,2,3,4,5,6',',')

轉自:

http://zhiwenweb.cn/jszx/sjkjs/mssql/201110/29418.html