5 ways to write smarter SQL

1. Putting Strings together

Putting two or more Strings together is pretty easy. Take String 1, use the Plus (+) sign and add String 2. Works fine and it is easy to read and understand.

To be honest, I still do it like this quite often. I guess it is just an old habit. But there is a smarter and safer way. Check this one:

declare @1 nvarchar(10) = null
declare @2 nvarchar(10) = 'Yes'

select 'Answer: ' + @1
select 'Answer: ' + @2

select concat('Answer: ', @1)
select concat('Answer: ', @2)

Using the concat function will produce a similar result. But is has one major advantage.

If you run this query, you will see that a Null value in one of the Strings will make the result also Null. That's because the + sign cannot handle the Null value.

The concat function however does handle the Null value by replacing it with an empty string. So if any of the values you want to put together might come from a nullable column? Be safe and use the concat function.

2. Avoid Nested IsNull Functions

Let's say you have 3 values. If value 1 is Null, then use value 2. Unless value 2 is also Null, then take value 3.

This is quite a common business case, for example with pricing. If there is a price agreement with the customer, take that price. If there is no price agreement, take the price from a special price list. And if there is no price list specified, just use the regular price.

Here is how the code might look, the good way and a way better way:

declare @1 nvarchar(10) = null
declare @2 nvarchar(10) = null
declare @3 nvarchar(10) = 'Yes'

select isnull(@1, isnull(@2, @3))
select coalesce(@1, @2, @3)

Now with one IsNull inside another IsNull, it is still pretty easy to understand. But how about a third, fourth or even a fifth one? The list goes on and on. Here you will certainly at one point run into an error that one of your brackets is missing or in the wrong place.

So why not use the coalesce function? It is way more easy to understand, saves you a lot of typing and most of all, no missing or misplaced brackets.

3. Just choose

This is also a common one. In the Database you have an option value like 1, 2 and 3. In the result you want to show the corresponding value but you cannot get it from another table.

Most often I see people using the Case Statement for this. Nothing wrong with that. It is just a lot of typing. Here is an easier way which is much shorter:

declare @option int = 1

select
case @option
  when 1 then 'Yes'
  when 2 then 'No'
  when 3 then 'Maybe'
end

select choose(@option, 'Yes', 'No', 'Maybe')

This is way much easier, right?

But wait... Sorry to tell you...

It only works for numerical values. And best if they are consecutive numbers. So if you have A, B and C, or 12, 198, 312? Sorry, but in the case (pun intended) use the Case Statement.

4. Avoid Nested Replaces

Replace is a great function to well... replace one character with another one. But that is also its limitation. You have to replace character one by one.

So let's say you have a string the has regular and straight brackets. But for some reason you only want curly brackets. Then you would need a Replace in a Replace in a Replace in a Replace.

Here is an easier way, using Translate:

declare @Text nvarchar(100) = 'Curly [brackets] (please)!'

select
  replace (
    replace (
      replace (
        replace (
          @Text, '[', '{'
            )
            , ']', '}'
          )
          , '(', '{'
        )
      , ')', '}'
  )

select translate(@Text, '[]()', '{}{}')

The only thing you need to watch out for is keep the number of characters from the second and third Parameter the same. Which makes it impossible to replace something with an empty string.

In that case, replace it with a character that is never used, like ~ or |. Then do a Replace to change that special character into an empty string.

It is not 100% perfect, but still way less lines and much more readable.

5. What month is it?

Also a common case I encounter on a regular basis. You have some Date and you want to show the name of the month in full writing. Oh, and also in a specific language.

With this one I also see a lot of Case Statements used to solve the problem. But let's not forget that the Format function is pretty powerfull and can save you a lot of typing.

Here is the difference:

declare @Date datetime = '2023-09-01'

select
case datepart(Month, @Date)
  when 1 then 'januari'
  when 2 then 'februari'
  when 3 then 'maart'
  when 4 then 'april'
  when 5 then 'mei'
  when 6 then 'juni'
  when 7 then 'juli'
  when 8 then 'augustus'
  when 9 then 'september'
  when 10 then 'oktober'
  when 11 then 'november'
  when 12 then 'december'
end

select format(@Date, 'MMMM', 'nl-NL')

Well, that makes life pretty easy, huh? And there are many languages to choose from. Find them using this query:

select * from syslanguages

That's it for today

There you go, 5 tips to make your code smarter, shorter and more robust. It was fun coming up with these examples, so you can expect more short tips like this in the future.

So stay tuned for more!

You can leave a comment or send a question to andre@andrespeek.com. Let me know if you got inspired and stay safe and healthy always!