判断包含相同数据的字段

  -- > --> (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.csdn.net/u/20080730/17/6a4cb5d7-4041-45f7-9654-c9997b423e05.html