Feeds:
Posts
Comments

Posts Tagged ‘SQL SERVER – How to concatenate int and varchar’

Concatenate int and varchar is a usual problem that we face in SQL scripting and the solution is easily available over the web. But what encourages me to write this article is, we can resolve this issue using a built in function in SQL Server 2012.
Let me show you the issue that we generally face when we concatenate int and varchar. Given below is the script.

Use AdventureWorks2012
GO
SELECT [DepartmentID] + ' ' + [Name] AS [Department ID & Name]
FROM [HumanResources].[Department]
GO
--OUTPUT

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value ‘Document Control’ to data type smallint.

Solution 1:
In the earlier version of SQL Server, we usually use CONVERT function to convert int into varchar and then concatenate it.
Given below is the script.

Use AdventureWorks2012
GO
SELECT CONVERT(VARCHAR(5),[DepartmentID]) + ' ' + [Name] 
AS [Department ID & Name]
FROM [HumanResources].[Department]
--OUTPUT

conactenate int to varchar1.1

Solution 2:
In this solution, we will use CONCAT function (a newly shipped function in SQL Server 2012) to convert int into varchar then concatenate it.
Given below is the script.

Use AdventureWorks2012
GO
SELECT CONCAT([DepartmentID] , ' ' , [Name] )
AS [Department ID & Name]
FROM [HumanResources].[Department]
--OUTPUT

conactenate int to varchar1.1

Conclusion :
I generally recommend solution 2 because as you can see that there is no difference in output between solution 1 and solution 2 but there is a remarkable difference between both solutions in performance. You can get the performance details from here.

Advertisements

Read Full Post »