Post
Ask Question

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.

  1. DECLARE @NullData Varchar(20) = null  
  2. 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
  1. DECLARE @NullData Varchar(20) = null  
  2. select  
  3. CONCAT  
  4. (  
  5. 'CONCAT Test With Null value ',  
  6. @NullData  
  7. ) 


Other Example
  1. DECLARE @CustomerTable AS TABLE   
  2. (  
  3. ID INT,  
  4. FirstName VARCHAR(20),  
  5. MiddleName VARCHAR(20),  
  6. LastName VARCHAR(20)  
  7. )   
  8. INSERT INTO @CustomerTable  
  9. VALUES(1, 'Jignesh', null, 'Trivedi') INSERT INTO @CustomerTable  
  10. VALUES(2, 'Murli', 'M', NULL) INSERT INTO @CustomerTable  
  11. VALUES  
  12. (3, null, 'Test', null)  
  13. SELECT  
  14. CONCAT(FirstName, ' ', MiddleName, ' ', LastName) as CustomerName  
  15. FROM  
  16. @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.


Similar Articles