CONCAT
MS SQL Server function CONCAT is introduced in MS SQL version 2012. Simple as that it returns a string that is the result of concatenating two or more string values.
Syntax:
CONCAT (string_value1, string_value2, … , [string_valueN])
- string_value - A string value to concatenate to the other values
The return type is a string, the length and type of which depend on the input. You can find more details about it on the official Microsoft docs site.
You can use this function for cases like the one described on this blog under MS SQL FORMATMESSAGE.
It requires a minimum of two input values, otherwise, an error is raised.
“Msg 189, Level 15, State 1, Line 1
The concat function requires 2 to 254 arguments.”
Example:
SELECT CONCAT('Damir') AS Result;
Let’s try to accomplish the same task with CONCAT as in the MS SQL FORMATMESSAGE article.
Example:
DECLARE @person_name nvarchar(max) = 'Damir';
DECLARE @drink_name nvarchar(max) = 'Gin and tonic';
DECLARE @number_of_drinks int = 3;
SELECT CONCAT(@person_name, ' will order ', @number_of_drinks, 'x ', @drink_name, '. Cheers!') AS Result;
It was a total success. But also like in the other post I personally don’t like the parameters and for me the code is much readable by using FORMATMESSAGE.
Let’s see how CONCAT handles NULL values.
Example:
DECLARE @person_name nvarchar(max) = 'Damir';
DECLARE @drink_name nvarchar(max) = NULL;
DECLARE @number_of_drinks int = 3;
SELECT CONCAT(@person_name, ' will order ', @number_of_drinks, 'x ', @drink_name, '. Cheers!') AS Result;
As you can see this is also nice. The NULL values are replaced by an empty string. There is no need for using ISNULL. Also, there is no need for casting the data because the input data types are automatically casted to string values (@number_of_drinks in this example).
And, finally let’s try to make the ultimate select statement from the MS SQL FORMATMESSAGE using CONCAT.
Example:
USE [WideWorldImporters];
-- Genertate drop statements for all user tables in database
SELECT CONCAT('DROP TABLE IF EXISTS [', SCHEMA_NAME(schema_id), '].[', name, '];') AS "Drop Statement" FROM sys.tables WHERE type = 'U';
CONCAT_WS
Now, let’s have a look at the new function CONCAT_WS which is introduced in MS SQL Server 2017.
Syntax:
CONCAT_WS (separator, argument1, argument2, … , [argumentN])
- Separator - Is a value of any character type (nvarchar, varchar, nchar, or char).
- Argument - Is a value of any type to be concatenated
The return type is a string, the length and type of which depend on the input.
Concatenates a variable number of arguments with a delimiter specified in the 1st argument (separator).
If you need the same separator between 2 or more strings, then CONCAT_WS is the command you are looking for.
It requires a minimum of three input values (a separator and two arguments), otherwise, an error is raised.
“Msg 189, Level 15, State 1, Line 1
The concat_ws function requires 3 to 254 arguments.”
Example:
SELECT CONCAT_WS(';', 'Value 1') AS Result;
Let's try to create a CSV file with all table names in our database.
Example:
USE [WideWorldImporters];
-- Genertate CSV file with table names
SELECT 'Schema name,Table name,Type desc' AS Result UNION ALL
SELECT CONCAT_WS(',', SCHEMA_NAME(schema_id), name, type_desc COLLATE database_default) FROM sys.tables WHERE type = 'U';
We accomplished the task easily and the output is exactly what we needed. Let’s have a look what’s happening if we have a NULL value.
Example:
USE [WideWorldImporters];
-- Genertate CSV file with table names
SELECT 'Schema name,Table name,Type desc' AS Result UNION ALL
SELECT CONCAT_WS(',', SCHEMA_NAME(schema_id), NULL, type_desc COLLATE database_default) FROM sys.tables WHERE type = 'U';
CONCAT_WS ignore NULL values in the columns, so this CSV would be wrong. If some of the columns are nullable we must use ISNULL function with default value.
If you like to complete this task by using CONCAT function you will be forced to put the separator value after each value, you would like to concatenate. That’s the main difference between the two functions.
*MS SQL Server sample database "WideWorldImporters" can be downloaded at: https://github.com/Microsoft/sql-server-samples/releases/tag/wide-world-importers-v1.0
*You can download the complete SQL Script with all examples from the post here: SQL Script.sql (1.62 kb)