declare @str varchar(800) declare @tb table(aname varchar(20)) set @str='257,258,259,257,259,320,321,336,321,' while charindex(',',@str)>0 begin insert into @tb select left(@str,charindex(',',@str)-1) set @str=stuff(@str,1,charindex(',',@str),'') end select * from @tb /*aname -------------------- 257 258 259 257 259 320 321 336 321(所影响的行数为 9 行) */
declare @str varchar(2000) set @str = '257,258,259,257,259,320,321,336,321'set @str = 'select ' + replace(@str,',',' col union all select ')exec (@str)
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='257,258,259,257,259,320,321,336,321' select * from dbo.fn_split(@s,',') drop function dbo.fn_split /*ID a ----------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1 257 2 258 3 259 4 257 5 259 6 320 7 321 8 336 9 321*/
/*按照符号分割字符串*/ 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),'') -- SET @c = substring(@c,charindex(' ',@c)+1,len(@c)) end insert @t(col) values (@c) return end
/*测试 select * from dbo.m_split('257,258,259,257,259,320,321,336,321',',') */
declare @str varchar(8000) set @str = '257,258,259,257,259,320,321,336,321' set @str = 'select ' + replace(@str,',',' col union all select ') exec (@str)
declare @s nvarchar(1000) set @s='257,258,259,257,259,320,321,336,321,'select b.col from (select col=convert(xml,'<r><v>'+replace(left(@s,len(@s)-1),',','</v><v>')+'</v></r>')) a outer apply (select col=C.v.value('.','nvarchar(10)') from a.col.nodes('/r/v') C(v)) b /* col ---------- 257 258 259 257 259 320 321 336 321(9 行受影响)*/
SQLServer拆分字符串的三种方法
declare @tb table(aname varchar(20))
set @str='257,258,259,257,259,320,321,336,321,'
while charindex(',',@str)>0
begin
insert into @tb
select left(@str,charindex(',',@str)-1)
set @str=stuff(@str,1,charindex(',',@str),'')
end
select * from @tb
/*aname
--------------------
257
258
259
257
259
320
321
336
321(所影响的行数为 9 行)
*/
declare @str varchar(2000)
set @str = '257,258,259,257,259,320,321,336,321'set @str = 'select ' + replace(@str,',',' col union all select ')exec (@str)
(
@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='257,258,259,257,259,320,321,336,321'
select * from dbo.fn_split(@s,',')
drop function dbo.fn_split
/*ID a
----------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 257
2 258
3 259
4 257
5 259
6 320
7 321
8 336
9 321*/
/*按照符号分割字符串*/
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),'')
-- SET @c = substring(@c,charindex(' ',@c)+1,len(@c))
end
insert @t(col) values (@c)
return
end
/*测试
select * from dbo.m_split('257,258,259,257,259,320,321,336,321',',')
*/
declare @str varchar(8000)
set @str = '257,258,259,257,259,320,321,336,321'
set @str = 'select ' + replace(@str,',',' col union all select ')
exec (@str)
set @s='257,258,259,257,259,320,321,336,321,'select b.col
from (select col=convert(xml,'<r><v>'+replace(left(@s,len(@s)-1),',','</v><v>')+'</v></r>')) a
outer apply (select col=C.v.value('.','nvarchar(10)') from a.col.nodes('/r/v') C(v)) b
/*
col
----------
257
258
259
257
259
320
321
336
321(9 行受影响)*/