As the name indicates, CONCAT() will concatenate two or more strings. This is one of the two string functions introduced in SQL Server 2012, FORMAT() being the other one.

New? Wait. Concatenating isn’t new. It’s always been there. Yeah. you are right. No, concatenating isn’t new.Yes, CONCAT() function is new.

“+” is used to concatenate strings, prior to 2012. As you can see in the query below, two strings are being concatenated.

SELECT 'HELLO'+SPACE(1)+'WORLD'

It gets tricky when you deal with non varchar data, and null values.

SELECT 1+SPACE(1)+'World' ;

SELECT ‘John’+NULL+‘Doe’ ;

These queries don’t return expected results. To fix that, select query should contain logic to replace null and convert int to string. On the left side of the below image is the corrected query.

CONCAT(), as can be seen in the right side, simplifies this process. It concatenates 2 or more strings, and automatically (implicitly) converts null to blank space and integer to string.

One more example to better explain the use of CONCAT().

SELECT CONCAT(1,1) ConcatInt
, CONCAT('John',NULL,'Doe') ConcatNull
, CONCAT(1,SPACE(1),'John') ConcatIntString

Lets wrap up…

Concat() is clearly a simpler way to concatenate strings. It improves code readability and is one of my favorite T-SQL enhancements.

~Sam.