SQL SERVER – NULL Values and CONCAT Function

I have been posting a lot of SQL in the Sixty Seconds video recently, however, today, we will be looking at a very interesting T-SQL CONCAT function and how it works with the NULL Values.

One of my clients who has worked with me previously Comprehensive Database Performance Health Check, recently sent me an email with a very interesting question and it was how to handle the NULL with the CONCAT function which I had written earlier.

Let us first look at the code below, it is using the Concat function. Without scrolling down much, try to guess what could be the answer of the same.

SELECT CONCAT (NULL, NULL, NULL)

Before the CONCAT function was introduced, we used to concat string with the help of + (plus) sign, as demonstrated in the following code.

SELECT NULL + NULL + NULL

Now guess the output of both of the queries. As it is difficult to print the answers here, I will be adding an image instead of a typed answer.

I hope it is clear from the image, that CONCAT function ignores all the NULL value which are passed to the function, whereas when you add NULL values with the plus (+) sign they actually return the answer as NULL.

This is very interesting indeed and as a developer, we should remember the difference between them. I have started to use the CONCAT function over any other function to concat the strings as it is very flexible and I do not have to worry about the datatypes when I am using it.

Would you be interesting to see this in action, if I create a video on this topic? Please leave a comment and I will follow what the majority suggests. If you do not want to comment, you can also send me an email at pinal@sqlauthority.com. Meanwhile, do not forget to check out my videos on SQL in Sixty Seconds.

Reference: Pinal Dave (https://blog.sqlauthority.com)