判断包含相同数据的字段

 --> --> (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