CHARINDEX and Find String Value

1

The below works as expected. Great. I have a form on our website that gets spammed a lot. I'm adding certain words and phrases in a Table: SpamTerms. If there's a match in the user's comments to any of the strings in my table, it reverts back to the form advising the user I think they are a spammer and to rephrase their text.

In the below example, one of the records is sunglasses. The below comments phrase gives me a MatchPosition = 6 and I can work with that. Great. However, how do I get the query to give me the actual word or phrase that matched? Maybe I'm doing this altogether wrong.

Declare @Comments varchar(MAX)
Set @Comments = 'test sunglasses test'
SELECT Sum(CHARINDEX(SpamTerms,@Comments)) AS MatchPosition
FROM SpamTerms

If I use this query, I get all records. I just want the one that matches.

Declare @Comments varchar(MAX)
Set @Comments = 'test sunglasses test'
SELECT Sum(CHARINDEX(SpamTerms,@Comments)) AS MatchPosition, SpamTerms
FROM SpamTerms
GROUP BY Spamterms

Thanks!

2

OK. I may have answered my own question by accident. This query works, but is it the right way to do this?

Set @Comments = 'test sunglasses test'
SELECT Sum(CHARINDEX(SpamTerms,@Comments)) AS MatchPosition, SpamTerms
FROM SpamTerms
GROUP BY Spamterms
HAVING Sum(CHARINDEX(SpamTerms,@Comments)) > 0

3

Yes, it is, it's the perfect way to do it.