Filter Bad Words in a SQL Server 2008 Database

If you are using SQL Server 2008, you can use Full-Text Search to filter bad words in a database. The procedure to do so is by creating Stopwords and Stoplists.

Stopwords - Commonly occurring strings that do not help the search and are discarded by SQL Server.

Stoplists - A stoplist is a list of stopwords that, when associated with a full-text index, is applied to full-text queries on that index.

Note: Full-Text Search must be enabled on your SQL Server for this to work (How to Detect If Full-Text Search is Enabled on a SQL Server Database)

You can create Language specific Stopwords. For eg: you can create a Stopword in a Stoplist for Spanish.

-- Create a StopList
CREATE FULLTEXT STOPLIST sampleStoplist;
GO

-- Add a Stopword to the StopList
ALTER FULLTEXT STOPLIST sampleStoplist
ADD 'mui' LANGUAGE 'Spanish';
GO

-- View the StopWord
SELECT * FROM sys.fulltext_stopwords

If you want to view all the default Stopwords present in your database in all Languages, fire this query:

SELECT * FROM sys.fulltext_system_stopwords

OUTPUT (Sample)

About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook