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.
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