In SQL Server, you can concatenate two or more strings by using the T-SQL CONCAT() function. You can also use SQL Server’s string concatenation operator (+) to do the same thing. Both are explained here.

In SQL Server (and in any computer programming environment), string concatenation is the operation of joining character strings end-to-end.

Here’s an example:

SELECT CONCAT('Peter', ' ', 'Griffin') AS 'Full Name';

Result:

Full Name    
-------------
Peter Griffin

Note that I actually concatenated 3 strings here. I concatenated the first name, the last name, plus a space.

If I didn’t add the space it would’ve looked like this:

SELECT CONCAT('Peter', 'Griffin') AS 'Full Name';

Result:

Full Name   
------------
PeterGriffin

Which may or may not be the result you’re looking for.

So if we apply this to a database, then the query might look something like this:

SELECT CONCAT(FirstName, ' ', LastName) AS 'Full Name'
FROM Individuals
WHERE IndividualId = '1';

Result:

Full Name    
-------------
Peter Griffin

If you’re concatenating more than two strings, and you need a space (or other separator), consider using the CONCAT_WS() function. This allows you to specify a separator to be used in between each string. You only need to specify the separator once, and it’s used on every string that’s concatenated, therefore saving you from having to re-type it between each string.

NULL Arguments

If you’re familiar with MySQL, you might know that it also has a CONCAT() function. However, one difference between SQL Server’s CONCAT() function and MySQL’s CONCAT() function is how they handle NULL arguments.

In MySQL, the CONCAT() function returns NULL if any argument is NULL. In SQL Server however, you can add a NULL argument without it resulting in a NULL result.

MySQL

Here’s what MySQL does:

SELECT CONCAT('Homer', NULL, 'Simpson') AS 'Full Name';

Result:

+-----------+
| Full Name |
+-----------+
| NULL      |
+-----------+

SQL Server

Here’s what SQL Server does:

SELECT CONCAT('Peter', NULL, 'Griffin') AS 'Full Name';

Result:

Full Name   
------------
PeterGriffin

Another Way to Concatenate – The String Concatenation Operator (+)

SQL Server provides another way to concatenate strings. You can use the + string concatenation operator.

SELECT 'Peter' + ' ' + 'Griffin' AS 'Full Name';

Result:

Full Name    
-------------
Peter Griffin