-- > --> (Roy)生成測試數據
set nocount on ;
if not object_id ( ' T1 ' ) is null
drop table T1
Go
Create table T1( [ a ] int , [ b ] nvarchar ( 13 ))
Insert T1
select 1 ,N ' 11,12,13 ' union all
select 2 ,N ' 32,45 ' union all
select 3 ,N ' 123,55 ' union all
select 4 ,N ' 48,56,564 ' union all
select 5 ,N ' 789,231,55,54 '
Go
-- > --> (Roy)生成測試數據
set nocount on ;
if not object_id ( ' T2 ' ) is null
drop table T2
Go
Create table T2( [ a ] int , [ b ] nvarchar ( 12 ))
Insert T2
select 1 ,N ' 4,98 ' union all
select 2 ,N ' 565,6541,687 ' union all
select 3 ,N ' 7,68,231 ' union all
select 4 ,N ' 89,35,45 ' union all
select 5 ,N ' 10,50 ' union all
select 6 ,N ' 90,70 '
Go
if 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
end
go
select
*
from
t1
where
exists ( select 1 from t2 where dbo.F_split( [ b ] ,t1. [ b ] ) = 1 )
a b
-- --------- -------------
2 32 , 45
5 789 , 231 , 55 , 54
转:http://topic.youkuaiyun.com/u/20080730/17/6a4cb5d7-4041-45f7-9654-c9997b423e05.html