试试这个函数:ALTER FUNCTION [dbo].[F_SplitString] ( @SplitString varchar(8000), -- 需要拆分的源字符串 @Separator varchar(8000) -- 分割字符 ) RETURNS @SplitStringsTable table ( [Order] int identity(1,1), [SubString] varchar(8000) ) AS BEGIN if (@SplitString = '') or (@Separator = '') begin insert into @SplitStringsTable ([SubString]) values ('') return end if (@Separator = '][') begin if (substring(@SplitString, 1, 1) = '[') and (substring(@SplitString, len(@SplitString), 1) = ']') begin select @SplitString = substring(@SplitString, 2, len(@SplitString) - 2) end end declare @CurrentIndex int declare @NextIndex int declare @SubString varchar(8000) select @CurrentIndex = 1 while(@CurrentIndex <= len(@SplitString) + 1) begin select @NextIndex = charindex(@Separator, @SplitString, @CurrentIndex) if (@NextIndex = 0 or @NextIndex is NULL) begin select @NextIndex = len(@SplitString) + 1 end select @SubString = substring(@SplitString, @CurrentIndex, @NextIndex - @CurrentIndex) insert into @SplitStringsTable ([SubString]) values (@SubString) select @CurrentIndex = @NextIndex + len(@Separator) end return END --测试:select * from F_SplitString ('[11][22][33]','][')
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,3,4,5,6,7,8,55' select * from dbo.fn_split(@s,',') drop function dbo.fn_split
(
@SplitString varchar(8000), -- 需要拆分的源字符串
@Separator varchar(8000) -- 分割字符
)
RETURNS @SplitStringsTable table
(
[Order] int identity(1,1),
[SubString] varchar(8000)
)
AS
BEGIN
if (@SplitString = '') or (@Separator = '')
begin
insert into @SplitStringsTable ([SubString]) values ('')
return
end
if (@Separator = '][')
begin
if (substring(@SplitString, 1, 1) = '[') and (substring(@SplitString, len(@SplitString), 1) = ']')
begin
select @SplitString = substring(@SplitString, 2, len(@SplitString) - 2)
end
end
declare @CurrentIndex int
declare @NextIndex int
declare @SubString varchar(8000)
select @CurrentIndex = 1
while(@CurrentIndex <= len(@SplitString) + 1)
begin
select @NextIndex = charindex(@Separator, @SplitString, @CurrentIndex)
if (@NextIndex = 0 or @NextIndex is NULL)
begin
select @NextIndex = len(@SplitString) + 1
end
select @SubString = substring(@SplitString, @CurrentIndex, @NextIndex - @CurrentIndex)
insert into @SplitStringsTable ([SubString]) values (@SubString)
select @CurrentIndex = @NextIndex + len(@Separator)
end
return
END
--测试:select * from F_SplitString ('[11][22][33]','][')
(
@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,3,4,5,6,7,8,55' select * from dbo.fn_split(@s,',') drop function dbo.fn_split