CONCAT Function in SQL Server 2012 and SQL Server 2014 - SQL Server Tutorial

We often have to concatenate string values those can be coming from variable, columns or even sometime we hard code string values to some column values. To concatenate those values we had been using + sign in older version such as SQL Server 2000, SQL Server 2005 and SQL Server 2008. In SQL Server 2012, Microsoft SQL Server came with new function call CONCAT that can be use to concatenate string values. Once good part of this function is if any value is Null, It does not produce Null output instead it ignores that and returns us all non Null values after concatenations.

Take a look in below script, How CONCAT Function works. I have a details video below if you would like to watch and see CONCAT function in action.

USE TestDB

go

CREATE TABLE dbo.CustomerAddress
  (
     FName       VARCHAR(100),
     LName       VARCHAR(100),
     HouseNumber INTEGER,
     StreetName  VARCHAR(100),
     City        VARCHAR(100),
     State       CHAR(2)
  )

go

INSERT INTO dbo.CustomerAddress
VALUES      ('Aamir',
             'Shahzad',
             123,
             'Test Street',
             'Charlotte',
             'NC')

go

INSERT INTO dbo.CustomerAddress
VALUES      ('John',
             'Smith',
             NULL,
             'ELM Street',
             'Charlotte',
             'NC')

go

--USE the Plus Sign to Concatenate String Values in SQL Server
SELECT *,
       Cast(HouseNumber AS VARCHAR(10)) + ' '
       + StreetName + ' ' + City + ' ' + State AS FullStreetAddress
FROM   dbo.CustomerAddress

--Use ISNULL To Handle Null Contatenation in SQL Serve when using Plus Sign
SELECT *,
       Isnull(Cast(HouseNumber AS VARCHAR(10)), '')
       + ' ' + StreetName + ' ' + City + ' ' + State AS FullStreetAddress
FROM   dbo.CustomerAddress

--USE the CONCAT Function to Concatenate String Values in SQL Server
SELECT *,
       Concat(Cast(HouseNumber AS VARCHAR(10)), ' ', StreetName, ' ', City, ' ', State)  
AS FullStreetAddress
FROM   dbo.CustomerAddress


CONCAT Function to Concatenate Strings in SQL Server 2012 and above