May 16, 2007 at 7:25 am
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.
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, Zadjil
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
May 16, 2007 at 8:41 am
Something like this?
SELECT Word FROM #RestrictedWords
WHERE CHARINDEX(Word, @TestWord) > 0
John
May 16, 2007 at 9:24 am
Thanks John, I had never come across CHARINDEX and it does just the job I want.
For clarity, and any others that are searching for the answer the resulting code is below.
Cheers,
Zadjil
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
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply