SQL Word Filter

Use this snippet inside a SQL 2000 stored procedure to filter content for rude words. You will require a table called FilterWord, with a column called Word containing the words to filter out. The filter works on whole words, not word parts. In the example below, the filter is checking @FromName, @ToName and @Message, it then sets @Response if a filter word is found.

Declare @Words Table(Word varchar(50))
insert into @Words select Word from FilterWord

DECLARE Words_Cursor CURSOR FOR
SELECT Word FROM @Words

OPEN Words_Cursor

FETCH NEXT FROM Words_Cursor into @word

WHILE @@FETCH_STATUS = 0
Begin
if (‘ ‘ + @FromName +’ ‘ like ‘% ‘ + @Word + ‘ %’) begin: set @Response = 2; break; end
if (‘ ‘ + @ToName + ‘ ‘ like ‘% ‘ + @Word + ‘ %’) begin: set @Response = 3; break; end
if (‘ ‘ + @Message+ ‘ ‘ like ‘% ‘ + @Word + ‘ %’) begin: set @Response = 4; break; end

FETCH NEXT FROM Words_Cursor into @word
End
CLOSE Words_Cursor
DEALLOCATE Words_Cursor

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: