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:
CREATE TABLE #Customers
INSERT INTO #Customers
SELECT 'Vincent', 'Vega'
SELECT 'Marsellus', 'Wallace'
SELECT 'Jules', NULL
-- Concatenate FirstName and LastName
SELECT FirstName + LastName
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:
Now we see that the we are returned with a string rather than a NULL.