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.

sql concatenate strings

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.

One comment
Thiru 29 Aug 2015 at 6:53 am

It’s great to have such an excellent turiaotl posted freely on the net. I so much benefited from this win server 2003 turiaotls and this reply is in appreciation for your wonderful work.I am about to seat for my CCNA and my next target is MCSE and i have found your turials very usefull.Please i look forward in anticipation for the posting of part 4 of the MCSE turiaotl Managing Hardware and Devices Thanks and God bless UUUUUUUUUU

Featured Articles

 Site Author

  • Thanks for visiting!