，sql中模糊匹配

求助，sql中模糊匹配！
有这样两个表：

表A
idsoft_name company
－－－－－－－－－－－－－－－－－－－－－－
Aoffice 2000 microsoft
Amcafee agent mcafee
Avnc33 vnc
Aqq2007 qq
Agame1 game1
Agame2 game2
Boffice 2003 microsoft
Bvnc4.0 vnc
Bmcafee8i mcafee
Bmicrosoft microsoft 
Bgame3 game3

表B
id name 
－－－－－－－－－－
1office
2mcafee
3vnc
4microsoft

现在要用一条SQL语句，从表A的soft_name中将含有表B中name（模糊匹配）的排除，得到如下结果：
idsoft_name company
－－－－－－－－－－－－－－－－－－
Aqq2007 qq
Agame1 game1
Agame2 game2
Bgame3 game3


help!!




  
 


[解决办法]

SQL code

--原始数据：@Adeclare @A table(id varchar(1),soft_name varchar(12),company varchar(9))insert @Aselect 'A','office 2000','microsoft' union allselect 'A','mcafee agent','mcafee' union allselect 'A','vnc33','vnc' union allselect 'A','qq2007','qq' union allselect 'A','game1','game1' union allselect 'A','game2','game2' union allselect 'B','office 2003','microsoft' union allselect 'B','vnc4.0','vnc' union allselect 'B','mcafee8i','mcafee' union allselect 'B','microsoft','microsoft' union allselect 'B','game3','game3'--原始数据：@Bdeclare @B table(id int,name varchar(9))insert @Bselect 1,'office' union allselect 2,'mcafee' union allselect 3,'vnc' union allselect 4,'microsoft'select * from @A a where not exists (select 1 from @B where charindex(name,a.soft_name)>0)/*id   soft_name    company   ---- ------------ --------- A    qq2007       qqA    game1        game1A    game2        game2B    game3        game3*/
[解决办法]
SQL code
declare @a table(id varchar(2),  soft_name  varchar(20) ,          company  varchar(20))insert @a select 'A',   'office 2000',           'microsoft'  union all select 'A',   'mcafee agent',          'mcafee'  union all select 'A',   'vnc33',                 'vnc'  union all select 'A',   'qq2007',                'qq'  union all select 'A',   'game1',                 'game1'  union all select 'A',   'game2',                 'game2'  union all select 'B',   'office 2003',           'microsoft'  union all select 'B',   'vnc4.0',                'vnc'  union all select 'B',   'mcafee8i',              'mcafee'  union all select 'B',   'microsoft',             'microsoft'   union all select 'B',   'game3',                 'game3'  declare @b table(id int,  name   nvarchar(20))insert @b select 1,    'office'  insert @b select 2,    'mcafee'  insert @b select 3,    'vnc'  insert @b select 4,    'microsoft'--或用patindex/charindex select a.* from @a a where not exists(select 1 from @b where  patindex( '%'+Name+'%',a.soft_name)>0 ) id   soft_name            company              ---- -------------------- -------------------- A    qq2007               qqA    game1                game1A    game2                game2B    game3                game3
[解决办法]
select a.* from @a a where not exists(select 1 from @b where  a.soft_name like '%'+Name+'%' )