select * from A where not exists(select 1 from B where charindex(B.col,A.col)>0)
select * from a ,b where charindex(cola,colb)<0
--SQL2005 select * from A except select * from B
select ta.* from ta,tb where ta.colname not like '%'+tb.colname+'%'
select * from A where not exists(select 1 from B where charindex(B.col,A.col)>0)
俺SQL盲,回帖真快,我先试验一下,对了过了在给分,多谢各位,嘿嘿~~
SELECT * FROM TB T WHERE NOT EXISTS(SELECT 1 FROM TB1 WHERE T.COL LIKE '%'+B1.COL+'%')
select * from tb1 where not exists(select 1 from tb2 where charindex(tb1.col,tb2.col)>0)
--> 测试数据:@a 现在要查询表A中的字符串不包含表B中字符串字段的行 declare @a table([cola] varchar(20)) insert @a select '12345.com.cn' union all select 'fdsafds' --> 测试数据:@b declare @b table([colb] varchar(20)) insert @b select 'com.cn' union all select 'gov.cn' select distinct a.* From @a a ,@b b where not exists(select 1 from @b where charindex(colb,a.cola)>0 )/* cola -------------------- fdsafds */
select * from A
excetp
select * from B
select * from A where not exists(select 1 from B where charindex(B.col,A.col)>0)
select * from A
except
select * from B
*
from
A
where
not exists(select 1 from B where charindex(B.col,A.col)>0)
WHERE NOT EXISTS(SELECT 1 FROM TB1 WHERE T.COL LIKE '%'+B1.COL+'%')
declare @a table([cola] varchar(20))
insert @a
select '12345.com.cn' union all
select 'fdsafds'
--> 测试数据:@b
declare @b table([colb] varchar(20))
insert @b
select 'com.cn' union all
select 'gov.cn'
select distinct a.* From @a a ,@b b where not exists(select 1 from @b where charindex(colb,a.cola)>0 )/*
cola
--------------------
fdsafds
*/