This post discusses the query you will need to be able to search inside a column in a table for any word or phrase that is not allowed. For example, you may have some restricted words or phrases that you cannot allow to be anywhere inside a particular column in a table. Perhaps these are profane words or keywords used in a programming language. This would be part of your data validation.

Let’s work with an example table that we need to search, and we’ll name the table Persons. Also we will have another table that contains a list of bad words or phrases and we’ll name that table BadWordsList. Below is the T-SQL script that will create the tables and the data in your non-production server.

Below is the query (and the results) you can use to show all of the rows that contain any of the bad words in the list.

Notice that this query uses the key word EXISTS. Notice that “return” is a restricted word, but the table has the word “returns”. The query simply looks a the string of characters, “return” and searches for that and will find that string within the word “returns” and therefore return true. You may want to first get a count of how many rows have words that are part of the restricted list and then display the rows if there are any. To do that we can just get a count first.

Two Words or NULL

If a row has two bad words and another row is NULL, the row with two bad words will be displayed and the one with NULL will not be displayed, as you would expect.