How to Concatenate

Concatenation in SQL Server is done by using the plus (+) operator.  Let’s setup a table and look at an example by concatenating customer’s first names and last names:
[cc lang=”sql”]
— Create Customers table
CREATE TABLE #Customers
(
FirstName varchar(50),
LastName varchar(50)
)

INSERT INTO #Customers
(
FirstName,
LastName
)
SELECT ‘Vincent’, ‘Vega’
UNION
SELECT ‘Marsellus’, ‘Wallace’
UNION
SELECT ‘Jules’, NULL

— Concatenate FirstName and LastName
SELECT FirstName + LastName
FROM #Customers
[/cc]

Now when we run the output we will see something interesting. When we try to concatenate a null value with a string, we are returned with a NULL value. This shows the important need to use the ISNULL function when concatenating values.

Let’s use the ISNULL function now to replace the NULL with an empty string:

[cc lang=”sql”]
SELECT ISNULL(FirstName, ”) + ISNULL(LastName, ”)
FROM #Customers
[/cc]

Now we see that the we are returned with a string rather than a NULL.