SQL Server Portal

Sometimes, we need to concatenate multiple fields to make a consolidated field. These fields may have same data type or multiple data types as well. In the past, we used multiple functions like ISNULL, CONVERT, CAST and used an Add (+) operator to concatenate it.

Now, we have a smart function called CONCAT() to achieve above mentioned scenario easily. This function is intelligent enough that to combine data of single / multiple data types. It converts all the data types to string before joining them together.

Let me show you some examples using old / new approaches.

Example 1: Concatenate multiple columns having NULL values

• Old Approach Using Add (+) Operator

In old approach, we simply apply add(+) operator to combine multiple fields as shown below.

USE Northwind
GO

SELECT [CompanyName]
     , [Address]
	 , [City]
	 , [Region]
	 , [Address] + ' ' + [City] + ' ' + [Region] AS [FullAddress]
FROM [dbo].[Suppliers];
GO
--OUTPUT

As you can see above, if one of the columns is having NULL value, which you are trying to concatenate, then result will be NULL regardless of which field has value or not.

To resolve NULL issue, we need to use ISNULL function to replace NULL values with empty space and ISNULL should be applied to all columns which we are trying to concatenate, since most of the time, we may not know which column may have NULL values, as shown below.

USE Northwind
GO

SELECT [CompanyName]
     , [Address]
	 , [City]
	 , [Region]
	 , ISNULL([Address],'') + ' ' + ISNULL([City],'') + ' ' + ISNULL([Region],'') AS [FullAddress]
FROM [dbo].[Suppliers];
GO
--OUTPUT

• New Approach Using CONCAT() Function :

Now, we have seen in earlier approach that how much effort required to concatenate fields. In the new approach, we just need to use a single function which is called CONCAT() function. This function allows us to concatenate multiple columns (varchar, int, datetime etc.) easily as shown in the example below.

USE Northwind
GO

SELECT [CompanyName]
     , [Address]
	 , [City]
	 , [Region]
	 , CONCAT([Address],' ',[City],' ',[Region]) AS [FullAddress]
FROM [dbo].[Suppliers];
GO
--OUTPUT

Example 2: Concatenate multiple columns having different data types

• Old Approach Using Add (+) Operator

Let’s concatenate multiple data types column using Add (+) operator as shown below but it generates the error since you cannot combine different data types of columns with Add (+) operator, how we were handling such cases earlier.

USE Northwind
GO

SELECT [SupplierID] 
     , [ContactName]
	 , [SupplierID] + ' ' + [ContactName] AS [SupplierID&Name]
FROM  [dbo].[Suppliers];
GO
--OUTPUT

In order to resolve this error, we need to use the CONVERT() function which will convert the int value to nvarchar datatype to make it same data type (string) for both columns and then concatenate it as shown below:

USE Northwind
GO

SELECT [SupplierID] 
     , [ContactName]
	 , CONVERT(NVARCHAR(50)
	 , [SupplierID]) + ' ' + [ContactName] AS [SupplierID&Name]
FROM   [dbo].[Suppliers];
GO
--OUTPUT

• New Approach Using CONCAT() function:

As we can see many hurdles and complexity in the old approach, in new approcah we need to simply apply CONCAT() function where it itself converts all columns data type to string and then concatenate it and retrun string as an output, as described in below example.

USE Northwind
GO

SELECT [SupplierID] 
     , [ContactName]
	 , CONCAT([SupplierID],' ',[ContactName]) AS [SupplierId&Name]
FROM   [dbo].[Suppliers];
GO
--OUTPUT

Conclusion:

I used CONCAT() function multiple times and found out extremly useful since it automatically handles NULL cases, different data type cases etc. If you use this function, do let me know your experience.

Read Full Post »