Today, I want to talk about the keyword NOT in SQL Server. It can be your friend or your worst enemy depending upon how you use it. Let’s delve into some examples of what I’m talking about and how it’s easy for people to mess it up.
Let’s say we have a table named DataTable. In the DataTable table, we have a field called FullName with what looks like people’s names in most cases. Sadly, somebody has been messing with it over the years so it has “special characters”, like $ or ) or even +, that shouldn’t be there for names. Our boss has finally given us the go-ahead to clean it up. The first thing we want to do is to find all of the data that has a “special character” in it.
One way we can do this is:
SELECT *
FROM DataTable
WHERE FullName LIKE '%$%'
OR FullName LIKE '%)%'
OR FullName LIKE '%@%'and so on and so on… I don’t know about y’all but I get tired of typing these out and you know there will be a “new” special character that the business wants you to look for soon. So the next option is?
SELECT *
FROM DataTable
WHERE FullName NOT LIKE '%[a-Z0-9]%'Great idea! Let’s use the power of RegEx to figure out what is a special character.
The only problem is that this doesn’t work like we want. It actually only finds those FullNames made up entirely of special characters. Notice that what you’re first finding is everything with data that has a letter or a number in it, then you take all of the data that is not in that group.
SELECT *
FROM DataTable
WHERE FullName LIKE '%[^a-Z0-9]%'This statement actually gives you what you want! The ^ is the secret sauce because it requests everything that is not a letter or number anywhere in the FullName field.
On a completely different note, but similar vein, I saw someone else’s code that’s not entirely wrong, but not really right either. This time I just want to show everything where FullName has data.
SELECT *
FROM DataTable
WHERE FullName IS NOT NULL
-- OR
SELECT *
FROM DataTable
WHERE NOT FullName IS NULL Most of us will write the query on top. It reads like natural language. The bottom one – not so much. Do both of them provide the same answer? Absolutely. Do both have the same execution plan making SQL Server work the same? Yes.
Which one is easier to maintain – especially if you have more than one developer that can work on it? The top one – without question. Why? Because that’s how most of us write the query and it reads like natural language.
Hopefully, you’ve seen how useful using NOT in the correct places can be – for your sanity as well as getting the correct data. Hopefully, you will experiment with your queries to find more efficient use of operators. Until next time my friends!