没太明白什么意思 但是给你一个函数 是分割字符串用的 估计这个函数对你会有用create function [dbo].[split] ( @SourceSql varchar(8000), @StrSeprate varchar(10) ) returns @temp table(F1 varchar(100)) as begin declare @i int declare @D varchar(10) set @SourceSql=rtrim(ltrim(@SourceSql)) set @i=charindex(@StrSeprate,@SourceSql) while @i>=1 begin if len(left(@SourceSql,@i-1))>0 begin set @D = substring(@SourceSql, 1, 1) if @D = 'D' begin insert @temp values(right(left(@SourceSql,@i-1),len(left(@SourceSql,@i-1)) - 1)) end else begin insert @temp values(left(@SourceSql,@i-1)) end end set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i) set @i=charindex(@StrSeprate,@SourceSql) end if @SourceSql<>'' insert @temp values(@SourceSql) return end例如:1,2,3,4,5 通过 select * from split('1,2,3,4,5',',') 会得到如下的表 |F1| |1| |2| |3| |4| |5|
declare @ta table (id int,fa varchar(100)) insert into @ta select 1,'1,2,3,4,5' union select 2,'5,7,6,1,8' union select 3,'7,5,4,8,7'declare @tb table (id int,fb varchar(100)) insert into @tb select 1,'4,5,6,8,9'select id,fa from (select id,fa,convert(xml,'<Root><v>'+replace(fa,',','</v><v>')+'</v></Root>') as fa_xml from @ta) a where exists( select * from @tb b cross apply (select c.v.value('.','varchar(10)') from a.fa_xml.nodes('/Root/v') c(v) ) d(v) where charindex(','+d.v+',',','+b.fb+',')>0 ) /* id fa ----------- --------------------------------------------- 1 1,2,3,4,5 2 5,7,6,1,8 3 7,5,4,8,7(3 行受影响) */
use tempdb goif object_id('F_split') is not null drop function F_split go create function F_split(@s nvarchar(100),@S2 nvarchar(100))--把字符数改为100 returns nvarchar(2) as begin select @s2=','+@s2+',',@s=@s+',' while @s>'' begin if charindex(','+left(@s,charindex(',',@s)),@s2)>0 return 1 set @s=stuff(@s,1,charindex(',',@s),'') end return 0 endgodeclare @ta table (id int,fa varchar(100)) insert into @ta select 1,'1,2,3,4,5' union select 2,'5,7,6,1,8' union select 3,'7,5,4,8,7'declare @Tb table([ID] int,[B] nvarchar(9)) Insert @Tb select 1,N'4,5,6,8,9'
select * from @ta a where exists(select 1 from @Tb where dbo.F_split([B],a.fa)=1)
(
@SourceSql varchar(8000),
@StrSeprate varchar(10)
)
returns @temp table(F1 varchar(100))
as
begin
declare @i int
declare @D varchar(10)
set @SourceSql=rtrim(ltrim(@SourceSql))
set @i=charindex(@StrSeprate,@SourceSql)
while @i>=1
begin
if len(left(@SourceSql,@i-1))>0
begin
set @D = substring(@SourceSql, 1, 1)
if @D = 'D'
begin
insert @temp values(right(left(@SourceSql,@i-1),len(left(@SourceSql,@i-1)) - 1))
end
else
begin
insert @temp values(left(@SourceSql,@i-1))
end
end
set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)
set @i=charindex(@StrSeprate,@SourceSql)
end
if @SourceSql<>''
insert @temp values(@SourceSql)
return
end例如:1,2,3,4,5 通过 select * from split('1,2,3,4,5',',')
会得到如下的表
|F1|
|1|
|2|
|3|
|4|
|5|
insert into @ta
select 1,'1,2,3,4,5' union
select 2,'5,7,6,1,8' union
select 3,'7,5,4,8,7'declare @tb table (id int,fb varchar(100))
insert into @tb select 1,'4,5,6,8,9'select id,fa
from (select id,fa,convert(xml,'<Root><v>'+replace(fa,',','</v><v>')+'</v></Root>') as fa_xml from @ta) a
where exists(
select *
from @tb b
cross apply (select c.v.value('.','varchar(10)')
from a.fa_xml.nodes('/Root/v') c(v)
) d(v) where charindex(','+d.v+',',','+b.fb+',')>0
)
/*
id fa
----------- ---------------------------------------------
1 1,2,3,4,5
2 5,7,6,1,8
3 7,5,4,8,7(3 行受影响)
*/
http://topic.csdn.net/u/20080730/17/6a4cb5d7-4041-45f7-9654-c9997b423e05.html
use tempdb
goif object_id('F_split') is not null
drop function F_split
go
create function F_split(@s nvarchar(100),@S2 nvarchar(100))--把字符数改为100
returns nvarchar(2)
as
begin
select @s2=','+@s2+',',@s=@s+','
while @s>''
begin
if charindex(','+left(@s,charindex(',',@s)),@s2)>0
return 1
set @s=stuff(@s,1,charindex(',',@s),'')
end
return 0
endgodeclare @ta table (id int,fa varchar(100))
insert into @ta
select 1,'1,2,3,4,5' union
select 2,'5,7,6,1,8' union
select 3,'7,5,4,8,7'declare @Tb table([ID] int,[B] nvarchar(9))
Insert @Tb
select 1,N'4,5,6,8,9'
select
*
from
@ta a
where
exists(select 1 from @Tb where dbo.F_split([B],a.fa)=1)