I am going to continue my series of very short articles or tidbits on Transaction SQL string functions. I will exploring the CONCAT() function today.
The CONCAT() function takes two or more string arguments and combines them into one string. Any arguements that are not strings are converted to a string. All NULL values are converted to the empty string ‘ ‘.
The following example takes seven input arguements of various data types and returns one string output.
1 2 3 4 5 6 7 8 |
-- Local variable declare @var_tmp varchar(32); -- Combine strings into one select @var_tmp = concat('Fi', 've', ' plus', NULL, ' four equals nine (', 9, ').'); -- Show the resulting string select @var_tmp as one_string; |
1 2 3 4 5 |
output: one_string -------------------------------- Five plus four equals nine (9). |
One thing to always worry about when using TSQL functions is how will it react with a UNKNOWN or NULL values?
In the example below, two NULL values equal one empty string.
1 2 3 4 5 6 7 8 |
-- Local variable declare @var_tmp varchar(32); -- Combine NULLS into one variable select @var_tmp = concat(NULL, NULL); -- Show the resulting string select @var_tmp as one_string; |
1 2 3 4 |
output: one_string -------------------------------- |
Next time, I will be exploring the DIFFERENCE() function.