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 391,142 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 3,186 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!

Please support our MS SQL advertiser:

Thread Tools Display Modes

Views: 1035 | Replies: 2 | Solved

•

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

•

Join Date: May 2006

Location: ★ ijug.net ★

Posts: 834

Reputation: ithelp is on a distinguished road

Rep Power: 4

Solved Threads: 61

ithelp

Offline

Practically a Posting Shark

Re: Checking for banned words

May 16th, 2007

May be you can add the possible combinations in database

★ Homework Help ★ Freelance Programmer ★ Freelance Jobs ★
★ Build A Website ★ Interview Question And Answer ★ Tax Filing ★

•

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

Re: Checking for banned words

May 16th, 2007

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

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