Using a word list with wildcards in SQL Server stored proc

I 开发者_如何学运维have a list of words that I want to exclude from some query results. The list of words is stored in a database table as a comma separated list. eg. bad, awful, worst. (Note: It isn't important that they are comma separated. They could just as easily be rows in a table).

I want to be able to utilise this list of words in a stored proc to ensure that keywords from this list don't appear anywhere in my search results. For example, given the following, how would I amend my query to ensure that all search results where filtered for words that appeared in my keywords exclusion list?

create table #keywords (column1 varchar(500))

insert #keywords values('bad, awful, worst')

create table #testData (column1 varchar(100))

insert #testData values('This is bad for you')
insert #testData values('This is good for you')
insert #testData values('This is awful for you')
insert #testData values('This is great for me')

SELECT * FROM #testData WHERE column1 like '%you%'

drop table #keywords

drop table #testData

As you can probably guess, I want to ensure each of the keywords in the exclusion list are used within wildcards (%) to ensure that the word doesn't appear anywhere in the search results. I am aware of the dangers of SQL injection also, and would be keen to see a way of doing this that minimises any risk.

Any help is appreciated. This will be run on SQL Server 2005 and above.

EDIT - My main concern is not the comma separated list, but more the way to use the values it contains along with wildcards to exclude particular items from my query results.

First of all, I wouldn't store the keywords as CSVs. You're in a database, use it!

Try something like:

create table #keywords (column1 varchar(500))

insert #keywords values('bad')
insert #keywords values('awful')
insert #keywords values('worst')

create table #testData (column1 varchar(100))

insert #testData values('This is bad for you')
insert #testData values('This is good for you')
insert #testData values('This is awful for you')
insert #testData values('This is great for me')

select *
from #testData t 
where column1 like '%you%'
  and not exists (
    select *
    from #keywords k
    where t.column1 like '%' + k.column1 + '%'
)

drop table #keywords

drop table #testData

Since you described that the keyword wordslist is there for a reason, I assume you are looking for a recusive solution. This is my surgestion:

DECLARE @keywords table (column1 VARCHAR(500)) 

INSERT @keywords VALUES('bad, awful, worst')

DECLARE @testData table (column1 VARCHAR(100)) 

INSERT @testData VALUES('This is bad for you')
INSERT @testData VALUES('This is good for you') 
INSERT @testData VALUES('This is awful for you') 
INSERT @testData VALUES('This is great for me') 

;WITH cte(column1, b, e, keyword) 
AS ( 
SELECT replace(column1, ' ', '') + ',' column1, 1 b, (charindex(',', column1 + ',')) e, cast('%' + substring(column1, 1, (charindex(',', column1 + ',')) - 1)+'%' as VARCHAR(50)) keyword
FROM @keywords
UNION ALL 
SELECT column1, e + 2,charindex(',', column1, e + 1), cast('%' + substring(column1, e + 1, charindex(',', column1, e + 1) - e - 1)+'%' as VARCHAR(50))
FROM cte ch
WHERE charindex(',', column1, e + 1) > 0
) 
SELECT *
FROM @testdata t
WHERE not exists (SELECT 1 FROM cte WHERE t.column1 like keyword)
and column1 like '%you%'