用下面的函数吧,包管用~ Create FUNCTION [dbo].[SplitToTable] ( @SplitString nvarchar(max), @Separator nvarchar(10)=' ' ) RETURNS @SplitStringsTable TABLE ( [id] int identity(1,1), [value] nvarchar(max) ) AS BEGIN DECLARE @CurrentIndex int; DECLARE @NextIndex int; DECLARE @ReturnText nvarchar(max); SELECT @CurrentIndex=1; WHILE(@CurrentIndex<=len(@SplitString)) BEGIN SELECT @NextIndex=charindex(@Separator,@SplitString,@CurrentIndex); IF(@NextIndex=0 OR @NextIndex IS NULL) SELECT @NextIndex=len(@SplitString)+1; SELECT @ReturnText=substring(@SplitString,@CurrentIndex,@NextIndex-@CurrentIndex); INSERT INTO @SplitStringsTable([value]) VALUES(@ReturnText); SELECT @CurrentIndex=@NextIndex+1; END RETURN; END select * FROm dbo.SplitToTable('A,B,C,D,E', ',')
/* 功能:实现split功能的函数 */create function dbo.fn_split ( @inputstr varchar(8000), @seprator varchar(10) ) returns @temp table (a varchar(200)) as begin declare @i intset @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) endif @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 /* a ---------------------- 1 2 3 4 5 6 7 8 55(所影响的行数为 9 行) */
create function [dbo].[m_split](@c varchar(2000),@split varchar(2)) returns @t table(col varchar(200)) as begin while(charindex(@split,@c)<>0) begin insert @t(col) values (substring(@c,1,charindex(@split,@c)-1)) set @c = stuff(@c,1,charindex(@split,@c),'') end insert @t(col) values (@c) return end
declare @sql varchar(20) set @sql='A,B,C,D,E' select * from dbo.m_split(@sql,',')/* col --------- A B C D E */
if object_id('f_split')is not null drop function f_split go create function f_split ( @s varchar(8000), --待分拆的字符串 @split varchar(10) --数据分隔符 )returns table as return ( select substring(@s,number,charindex(@split,@s+@split,number)-number)as col from master..spt_values where type='p' and number<=len(@s+'a') and charindex(@split,@split+@s,number)=number ) go select * from dbo.f_split('11,2,3',',') /* col ---- 11 2 3 (3 個資料列受到影響) */
Create FUNCTION [dbo].[SplitToTable]
(
@SplitString nvarchar(max),
@Separator nvarchar(10)=' '
)
RETURNS @SplitStringsTable TABLE
(
[id] int identity(1,1),
[value] nvarchar(max)
)
AS
BEGIN
DECLARE @CurrentIndex int;
DECLARE @NextIndex int;
DECLARE @ReturnText nvarchar(max);
SELECT @CurrentIndex=1;
WHILE(@CurrentIndex<=len(@SplitString))
BEGIN
SELECT @NextIndex=charindex(@Separator,@SplitString,@CurrentIndex);
IF(@NextIndex=0 OR @NextIndex IS NULL)
SELECT @NextIndex=len(@SplitString)+1;
SELECT @ReturnText=substring(@SplitString,@CurrentIndex,@NextIndex-@CurrentIndex);
INSERT INTO @SplitStringsTable([value]) VALUES(@ReturnText);
SELECT @CurrentIndex=@NextIndex+1;
END
RETURN;
END
select * FROm dbo.SplitToTable('A,B,C,D,E', ',')
功能:实现split功能的函数
*/create function dbo.fn_split
(
@inputstr varchar(8000),
@seprator varchar(10)
)
returns @temp table (a varchar(200))
as begin
declare @i intset @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)
endif @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 /*
a
----------------------
1
2
3
4
5
6
7
8
55(所影响的行数为 9 行)
*/
create function [dbo].[m_split](@c varchar(2000),@split varchar(2))
returns @t table(col varchar(200))
as
begin
while(charindex(@split,@c)<>0)
begin
insert @t(col) values (substring(@c,1,charindex(@split,@c)-1))
set @c = stuff(@c,1,charindex(@split,@c),'')
end
insert @t(col) values (@c)
return
end
declare @sql varchar(20)
set @sql='A,B,C,D,E'
select * from dbo.m_split(@sql,',')/*
col
---------
A
B
C
D
E
*/
go
create function f_split
(
@s varchar(8000), --待分拆的字符串
@split varchar(10) --数据分隔符
)returns table
as
return
(
select substring(@s,number,charindex(@split,@s+@split,number)-number)as col
from master..spt_values
where type='p' and number<=len(@s+'a')
and charindex(@split,@split+@s,number)=number
)
go
select * from dbo.f_split('11,2,3',',')
/*
col
----
11
2
3
(3 個資料列受到影響)
*/