Checking for banned words

•

What is DaniWeb IT Discussion Community?

You're currently browsing the MS SQL section within the Web Development category of DaniWeb, a massive community of 402,028 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 2,456 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.

Join our community!

Checking for banned words

•

Join Date: May 2007

Posts: 8

Reputation: Zadj is an unknown quantity at this point

Rep Power: 0

Solved Threads: 0

Zadj

Offline

Newbie Poster

Checking for banned words

May 16th, 2007

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:

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
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
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

DaniWeb Home > Forums > Web Development > Databases > MS SQL > Checking for banned words

Checking for banned words

Checking for banned words

Checking for banned words