CONCAT Function and NULL Values in SQL Server 2012

Problem statement

The "+" operator is also used for string concatenation in SQL Server. A problem with this operator is that it is not able to handle null values hence the result of concatenating a string value and a null string is always null.

DECLARE @NullData Varchar(20) = null
SELECT 'This is my test data' + @NullData

One of the solutions is to use the “ISNULL” function to convert a null value to an empty string value.



Solution

SQL Server 2012 introduced the string concatenation function "CONCAT". This string function can help us to resolve this problem without use of the ISNULL function.

Introduction of the “CONCAT” function

The CONCAT string function was introduced with SQL Server 2012. The CONCAT string function is used to concatenate two or more strings into one string. CONCAT takes string arguments as input and concatenates these string inputs into a single string. It requires a minimum of two strings as input, else it raises a compile time error. Here all arguments (inputs) are converted into a string type implicitly.

Example

DECLARE @NullData Varchar(20) = null
select
CONCAT
(
'CONCAT Test With Null value ',
@NullData
)

Other Example

DECLARE @CustomerTable AS TABLE
(
ID INT,
FirstName VARCHAR(20),
MiddleName VARCHAR(20),
LastName VARCHAR(20)
)
INSERT INTO @CustomerTable
VALUES(1, 'Jignesh', null, 'Trivedi') INSERT INTO @CustomerTable
VALUES(2, 'Murli', 'M', NULL) INSERT INTO @CustomerTable
VALUES
(3, null, 'Test', null)
SELECT
CONCAT(FirstName, ' ', MiddleName, ' ', LastName) as CustomerName
FROM
@CustomerTable

Output



CONCAT requires a minimum of two inputs values, otherwise SQL Server raises a compile-time warning.



It allows a maximum of 254 inputs values. If an input variable is more than 254 then SQL Server raises a runtime exception.



The CONCAT function accepts every type of argument and it implicitly converts to strings. For example an “int” is converted into a string length of 12, the same as a float that is converted to a string length of 32.

A Large Object (LOB) type is not supported by this function. The return type is truncated to 8000 in length regardless of the return type.

Summary

The CONCAT string function concatenates two or more inputs and by using this, we need not worry about NULL value handling.