How to Concatenate String and Integer Values in SQL Server

How to Concatenate String and Integer Values in SQL Server

It's fun to work with databases. Every day, we save data into tables in various columns, and frequently, to create our final output, we must write queries that concatenate numerous columns of various datatypes. The #Customer Table in the example below has a column with the street address as a string and the zip code as an INT. We want to combine these columns to create Complete Address. The + sign can be used to concatenate, but it only functions with string values.

 

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