How to Concatenate String and Integer Values in SQL Server ( + Sign vs CONCAT ) - TSQL Tutorial

Working with Databases is fun. On daily basis we save data into tables in different columns and often we need to write queries which involve concatenation of multiple columns of different datatypes to produce our final output. In below example we have saved some data into #Customer Table which has Street Address column as string and zip as INT. we want to produce Full Address by concatenating these columns. To concatenate we can use + sign but this works only with String values. So if we have any Integer value/s we have to convert them to String first. We can use Cast or Convert function to convert Integer value to string. Also if there is any Null value among which values we are concatenating , our output will be Null by using + sign. To take care of that part we can use either ISNULL() or Coalesce Function to replace NULL with blank value in SQL Server.

In SQL Server 2012 and Later versions, CONCAT function was introduced. You can concatenate multiple values by using this function. Fun part of this function is you don't have to worry about Null outcome as it will Ignore Null values and also you don't have to worry about converting INT into strings, It will take care of that automatically. So if you are using new versions such as 2012 or later, start taking advantage of this function.

Create table #TempCustomer (
Id int, Name VARCHAR(100),
 StreetAddress VARCHAR(100), 
 Zip Int, 
 City VARCHAR(100))
go
Insert into #TempCustomer 
Select 1,'Aamir','House# 000 Street ABC',28000,'Charlotte'
Union 
Select 2,'Raza','House# 001 Street PJK',45000,'New York'
Union 
Select 2,'Raza','House# 011 Street ATC',7500,'Harisburg'
Union 
Select 2,'Raza','House# 222 Street ZZZ',2500,NULL
Union 
Select 2,'Raza','House# 222 Street ZZZ',Null,'High Points'

--By using Plus ( Sign)
Select StreetAddress+' '+Cast(Zip AS VARCHAR(10))+' '+ City 
AS FullAddress from #TempCustomer

--Have to Take care of Null Values by ourselves by using ISNULL 
Select ISNULL(StreetAddress,'')+' '+ ISNULL(Cast(Zip AS VARCHAR(10)),'')+' '+ 

ISNULL(City,'') 
AS FullAddress from #TempCustomer

--Have to Take care of Null Values by ourselves by using Coalesce 
Select Coalesce(StreetAddress,'')+' '+ Coalesce(Cast(Zip AS VARCHAR(10)),'')+' '+ 

Coalesce(City,'') 
AS FullAddress from #TempCustomer


--USE CONCAT Function in SQL 2012 and Later Version, NULL values will be 
--ignored and we don't have to convert Zip( INT To VARCHAR), It will take care of conversion

Select Concat(StreetAddress,' ',Zip,' ',City) AS FullAddress from #TempCustomer

drop table #TempCustomer

Output from above Queries for String  and Integer concatenation in SQL Server



TSQL Video Tutorial : How to use + Sign or CONCAT Function in SQL Server for Concatenation