SQL - CONCAT function

CONCAT function is also known as T-SQL function which was introduced in SQL Server 2012. This function allows us to concatenate two or more parameters values together and these parameters values should be separated by comma. Before release of Concat function, we used to use the “+” operator to combine or concatenate two or more string values. The most important feature of Concat function is that it also takes care of data type conversion and beautifully handles NULL on its own. In case of Concat function, we don't need to care about the null values in the parameters which are going to be used.

Syntax CONCAT ( string_value1, string_value2 [, string_valueN ] ) Arguments string_value: A string value to concatenate to the other values. Return Types: String, the length and type of which depend on the input.

Interesting features

1. CONCAT takes a variable number of string arguments and concatenates them into a single string.
2. It requires at least two input values; otherwise, an error is raised.
3. All arguments are implicitly converted to string types and then concatenated.
4. Best part of this; Null values are implicitly converted to an empty string. If all the arguments are null, an empty string of type varchar(1) is returned.
5. The implicit conversion to strings follows the existing rules for data type conversions. 
6. If none of the input arguments is of a supported large object (LOB) type, then the return type is truncated to 8000 in length, regardless of the return type. This truncation preserves space and supports efficiency in plan generation.

Examples:

Concatenate Space Characters with input variables

To understand the features of Concat function, we are showing the examples with Concat function and without the concat functions as given below:

---- Declare local variables Declare @inpFirstName Varchar(20) ='Ryan' Declare @inpLastName Varchar(20) ='Arjun' ---- Concatenating variables without Concat Function Select @inpFirstName+' '+@inpLastName as FullName FullName Ryan Arjun ---- Concatenating variables with Concat Function Select CONCAT(@inpFirstName,' ',@inpLastName) as FullName FullName Ryan Arjun

In the above example, we are using the two variables to concatenating together. You can see that, we are getting the same outputs. 

Concatenate Space Characters with with NULL values

Now, we are going to make some interesting changes in the above example by setting the null value in the second variable as given below:

---- Declare local variables Declare @inpFirstName Varchar(20) ='Ryan' ---- Set Null Value here Declare @inpLastName Varchar(20) =NULL ---- Concatenating variables without Concat Function Select @inpFirstName+' '+@inpLastName as FullName FullName NULL ---- Concatenating variables with Concat Function Select CONCAT(@inpFirstName,' ',@inpLastName) as FullName FullName Ryan

You can easily see that full name is showing NULL values in case of without Concat function but by using Concat function, full name is showing because of Null values are implicitly converted to an empty string.

Concatenate number and string together

If you want to concatenate string with numeric value then you should need to convert the numeric value into the string as given below:

---- Declare local variables Declare @inpFirstName Varchar(20) ='Ryan' Declare @inpContactNumber BIGINT =1234567890 ---- Concating variables without Concat Function Select @inpFirstName+':'+CAST(@inpContactNumber as Varchar) as NameWithContact NameWithContact Ryan:1234567890 ---- Concatenating variables with Concat Function Select CONCAT(@inpFirstName,':',CAST(@inpContactNumber as Varchar)) as NameWithContact NameWithContact Ryan:1234567890

If you want to concatenate two numeric values then there is no need to change their data types because of implicit conversion to strings follows the existing rules for data type conversions.

Conclusion

The CONCAT function appends one string to the end of another string and does not require ISNULL for converting NULL into an empty string. All arguments are implicitly converted to string types and then concatenated.