Checking for banned words

[Zadj]

Hi,

I've run into a situation where I can not see how to write efficient code to perform a check that I need to do. I am running on MSSQL 2005

The check is to compare a varchar passed in to the stored procedure against the words in a table. The catch is that it is not the full word that needs to be checked but any part of the word. This is to stop the restricted words appearing in the text passed in. (eg the banned word test in the text 'this is a testing sentence'). I have got code that works but is very inefficient as it works through each word in the banned words and compares these against the supplied word/sentence.

Any advice on how I could do this more efficiently would be greatly appreciated.

Thanks, Zadj

ps. Here is the code that I am currently using:

DECLARE @Result INT
DECLARE @Testword VARCHAR(50)
SET @Testword = 'Testword'

CREATE TABLE #RestrictedWords (
Word VARCHAR(50)
)

--Populate temp table from the settings table
/*INSERT INTO #RestrictedWords
SELECT * FROM SettingsRestrictedWords*/
--Use test entries just now
INSERT INTO #RestrictedWords
(Word)
VALUES ('test')
INSERT INTO #RestrictedWords
(Word)
VALUES ('dog')

--Get first restricted word
DECLARE @CurBadWord VARCHAR(50)
SET @CurBadWord = (SELECT TOP 1 Word FROM #RestrictedWords)

WHILE (@CurBadWord IS NOT NULL)
 BEGIN
    -- Test the word and return the error code if  match
    IF (@Testword LIKE '%' + @CurBadWord + '%')
        SET @Result = -2

    -- Move to the next word
    DELETE FROM #RestrictedWords WHERE Word = @CurBadWord
    SET @CurBadWord = (SELECT TOP 1 Word FROM #RestrictedWords)
 END
--Clean up
DROP TABLE #RestrictedWords

SELECT @Result  -- Return the result

[ithelp]

May be you can add the possible combinations in database

[Zadj]

Thanks for the reply.

I've been given the answer on a different forum. The tip is CHARINDEX

In case anyone is interested here is the resulting code

DECLARE @Result INT
DECLARE @Testword VARCHAR(50)
SET @Testword = 'Tesword'

CREATE TABLE #RestrictedWords (
Word VARCHAR(50)
)

--Populate temp table from the settings table
/*INSERT INTO #RestrictedWords
SELECT * FROM SettingsRestrictedWords*/
--Use test entries just now
INSERT INTO #RestrictedWords
(Word)
VALUES ('test')
INSERT INTO #RestrictedWords
(Word)
VALUES ('dog')

/* Here is the changed code */
SELECT @Result=-2 FROM #RestrictedWords
WHERE CHARINDEX(Word, @TestWord) > 0
/***********************/

--Clean up
DROP TABLE #RestrictedWords

SELECT @Result -- Return the result