select * from dbo.fn_split('70,532,10,55,68',',') union ----用union就可以过滤了 select * from dbo.fn_split('10,532,698',',')a ------------------------- 10 532 55 68 698 70(6 row(s) affected)
其实如果就是两个一组来比较是没有问题的,像下面这样就可以了SELECT res=STUFF((SELECT ','+ a FROM ( select * from dbo.fn_split('70,532,10,55,68',',') union select * from dbo.fn_split('10,532,698',',') )m FOR XML PATH('') ),1,1,'')res ---------------------------- 10,532,55,68,698,70(1 row(s) affected)
--> 测试数据: #T if object_id('tempdb.dbo.#T') is not null drop table #T create table #T (id int,name varchar(8)) insert into #T select 1,'jame,job' union all select 2,'paul,mc' union all select 3,'carl';--> 1. CTE 递归找分隔字符位置法:速度极快 with T (id,P1,P2) as ( select id,charindex(',',','+name),charindex(',',name+',')+1 from #T union all select a.id,b.P2,charindex(',',name+',',b.P2)+1 from #T a join T b on a.id=b.id where charindex(',',name+',',b.P2)>0 ) select a.id,name=substring(a.name+',',b.P1,b.P2 - b.P1 - 1) from #T a join T b on a.id=b.id order by 1 /* id name ----------- --------- 1 jame 1 job 2 mc 2 paul 3 carl */ --> 测试数据: #T if object_id('tempdb.dbo.#T') is not null drop table #T create table #T (id int,name varchar(8)) insert into #T select 1,'jame,job' union all select 2,'paul,mc' union all select 3,'carl';--> 2. 临时表法:速度比CTE方法相差无几 if object_id('tempdb.dbo.#') is not null drop table # select top 8000 id=identity(int,1,1) into # from syscolumns,sysobjects --> select max(len(name)) from #T=11 select a.id,name=substring(a.name+',',b.id,charindex(',',a.name+',',b.id+1)-b.id) from #T a inner join # b on substring(','+a.name,b.id,1)=',' /* id name ----------- --------- 1 jame 1 job 2 mc 2 paul 3 carl */3. XML法:速度较慢 select a.id,b.name from (select id,name=convert(xml,'<root><v>'+replace(name,',','</v><v>')+'</v></root>') from #T ) a outer apply (select name=N.v.value('.','varchar(100)') from a.name.nodes('/root/v') N(v)) b /* id name ----------- --------- 1 jame 1 job 2 mc 2 paul 3 carl */
Create function [dbo].[fn_split](@inputstr nvarchar(4000), @seprator varchar(10))
returns @temp table (a nvarchar(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----使用:
select * from dbo.fn_split('70,532,10,55,68',',')a
---------------------------
70
532
10
55
68(5 row(s) affected)
EXCEPT
select * from dbo.fn_split('10,532,698',',')a
------------------------
55
68
70(3 row(s) affected)
否则就回归到C/C++来处理了,虽然sql也可以用类似while之类的来达到目的
字符串1
str1 = '10,532,698'
字符串2
str2 = '70,532,10,55,68'set str3 = str1 + str2 --变成了'10,532,698,70,532,10,55,68'如何得到10,532,698,70,68
就是过滤了重复的
union ----用union就可以过滤了
select * from dbo.fn_split('10,532,698',',')a
-------------------------
10
532
55
68
698
70(6 row(s) affected)
(
select * from dbo.fn_split('70,532,10,55,68',',')
union
select * from dbo.fn_split('10,532,698',',')
)m FOR XML PATH('')
),1,1,'')res
----------------------------
10,532,55,68,698,70(1 row(s) affected)
--> 测试数据: #T
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (id int,name varchar(8))
insert into #T
select 1,'jame,job' union all
select 2,'paul,mc' union all
select 3,'carl';--> 1. CTE 递归找分隔字符位置法:速度极快
with T (id,P1,P2) as
(
select id,charindex(',',','+name),charindex(',',name+',')+1 from #T
union all
select a.id,b.P2,charindex(',',name+',',b.P2)+1 from #T a join T b on a.id=b.id where charindex(',',name+',',b.P2)>0
)
select a.id,name=substring(a.name+',',b.P1,b.P2 - b.P1 - 1) from #T a join T b on a.id=b.id order by 1
/*
id name
----------- ---------
1 jame
1 job
2 mc
2 paul
3 carl
*/
--> 测试数据: #T
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (id int,name varchar(8))
insert into #T
select 1,'jame,job' union all
select 2,'paul,mc' union all
select 3,'carl';--> 2. 临时表法:速度比CTE方法相差无几
if object_id('tempdb.dbo.#') is not null drop table #
select top 8000 id=identity(int,1,1) into # from syscolumns,sysobjects --> select max(len(name)) from #T=11
select a.id,name=substring(a.name+',',b.id,charindex(',',a.name+',',b.id+1)-b.id) from #T a inner join # b on substring(','+a.name,b.id,1)=','
/*
id name
----------- ---------
1 jame
1 job
2 mc
2 paul
3 carl
*/3. XML法:速度较慢
select
a.id,b.name
from
(select id,name=convert(xml,'<root><v>'+replace(name,',','</v><v>')+'</v></root>') from #T ) a
outer apply
(select name=N.v.value('.','varchar(100)') from a.name.nodes('/root/v') N(v)) b
/*
id name
----------- ---------
1 jame
1 job
2 mc
2 paul
3 carl
*/