62,046
社区成员
发帖
与我相关
我的任务
分享
create function f_Compare
(
@tel1 varchar(100),@tel2 varchar(100)
)
returns int
as
begin
declare @t varchar(12)
set @tel1 = @tel1+','
while patindex('%,%',@tel1)<>0
begin
set @t = substring(@tel1,0,patindex('%,%',@tel1))
if patindex('%'+@t+'%',@tel2)<>0
begin
return 1
break
end
set @tel1 = substring(@tel1,patindex('%,%',@tel1)+1,len(@tel1))
end
return 0
end
-------测试数据--------
declare @tbl table
(
cus nchar(1),
tel varchar(100)
)
insert into @tbl
select 'a', '010-111111,15811223344'
union all
select 'f', '15811223344'
union all
select 'b', '010-111111'
union all
select 'c', '010-111112,15811223344'
union all
select 'd', '010-111111,15811223345'
union all
select 'e', '010-111112,15811223345'
union all
select 'h', '010-111113,15811223346'
-------测试数据--------
select * from @tbl n where n.cus not in(
select distinct a.cus from @tbl a cross join @tbl b
where a.cus<>b.cus and dbo.f_Compare(a.tel,b.tel)=1
)