http://www.cnblogs.com/liqiang665/archive/2007/06/06/773622.aspx
if object_id(’f_split’) is not null
drop function f_split
go
create function f_split(@aString varchar(8000),@pattern varchar(10))
returns @temp table(a varchar(100))
--实现split功能 的函数
--说明:@aString,字符串,如a:b:c;@pattern,分隔标志,如 :
as
begin
declare @i int
set @aString=rtrim(ltrim(@aString))
set @i=charindex(@pattern,@aString)
while @i>=1
begin
insert @temp values(left(@aString,@i-1))
set @aString=substring(@aString,@i+1,len(@aString)-@i)
set @i=charindex(@pattern,@aString)
end
if @aString<>’’
insert @temp values(@aString)
return
end
--运行下上述代码,然后就可以用了,例子:
select * from dbo.f_split(’0:418001:418002:418002’,':')
Create FUNCTION [dbo].[Fun_Split]
(
@str varchar(500), --要分割的字符串
@split varchar(100), --分隔符
@n int --要返回第几个子字符串
)
RETURNS varchar(500)
AS
BEGIN
DECLARE @i int --循环因子
declare @k int --计数因子
set @k=1
set @str=ltrim(rtrim(@str))
set @i=charindex(@split,@str)
while @i>1
begin
if @k=@n
begin
return(rtrim(ltrim(left(@str,@i-1))))
end
set @str=substring(@str,@i+1,len(@str)-@i)
set @i=charindex(@split,@str)
set @k=@k+1
end
return rtrim(ltrim(@str))
END
--运行下上述代码,然后就可以用了,例子:
select * from dbo.Fun_Split(’0:418001:418002:418002’,':','2')