In this tutorial, I will explain how to use concat function in SQL 2016. 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) ='John'
Declare @inpLastName Varchar(20) ='Carter'
---- Concatenating variables without Concat Function
Select @inpFirstName+' '+@inpLastName as FullName
---- Concatenating variables with Concat Function
Select CONCAT(@inpFirstName,' ',@inpLastName) as FullName
|
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) ='John'
---- Set Null Value here
Declare @inpLastName Varchar(20) =NULL
---- Concatenating variables without Concat Function
Select @inpFirstName+' '+@inpLastName as FullName
---- Concatenating variables with Concat Function
Select CONCAT(@inpFirstName,' ',@inpLastName) as FullName
|
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) ='John'
Declare @inpContactNumber BIGINT =1234567890
---- Concating variables without Concat Function
Select @inpFirstName+':'+CAST(@inpContactNumber as Varchar) asNameWithContact
|
NameWithContact
|
|
John :1234567890
|
---- Concatenating variables with Concat Function
Select CONCAT(@inpFirstName,':',CAST(@inpContactNumber as Varchar))as NameWithContact
|
NameWithContact
|
|
John: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.
HostForLIFE.eu SQL 2016 Hosting
European best, cheap and reliable ASP.NET hosting with instant activation. HostForLIFE.eu is #1 Recommended Windows and ASP.NET hosting in European Continent. With 99.99% Uptime Guaranteed of Relibility, Stability and Performace. HostForLIFE.eu security team is constantly monitoring the entire network for unusual behaviour. We deliver hosting solution including Shared hosting, Cloud hosting, Reseller hosting, Dedicated Servers, and IT as Service for companies of all size.
