SqlServer 2008 - deleting rows containing terms found in a black list table

I have two tables, one is a list of strings and the other contains a list of black listed words. I wish to find all the rows in the first table that their string contains a word from the second.

using like statement I created the following script and it worked:

select a.string

from A a

left join BlackList b on a开发者_开发问答.string like '%' + b.Term + '%'

where b.Term is null

I wanted to use full text search to get better performance. I was looking for a way to do this: select a.string

from A a

left join BlackList b on contains(a.string, b.Term)

where b.Term is null

My research on the web discovered this is not possible, is their another way of doing this?

You need to run row by row so try CROSS APPLY. No match = no row from the CROSS APPLY query which is the same as LEFT JOIN..IS NULL

select
   a.string
from
   A a
   CROSS APPLY
   (SELECT b.Term FROM BlackList b WHERE contains(a.string, b.Term)) foo