TSQL Concatenation + operator vs CONCAT vs CONCAT_WS

Concatenation in TSQL is often handled using the + operator. However, using + for concatenation requires the coder to account for data types and NULLs.

There’s also the CONCAT function (released with SQL Server 2012), and the CONCAT_WS function (released in 2017). Both offer advantages over the + operator in terms of NULLS and cleaner code, but require a few considerations:

• The + operator CONCAT concatenates a list of values.
• CONCAT can accept up to 254 arguments.
• Nulls are converted to empty strings and data types that can be implicitly converted to a string type are converted.

In General:

Returned Data Type: nvarchar(max)

  Any argument of:

• SQL-CLR system type
• SQL-CLR UDT
• nvarchar(max)

Returned Data Type: varchar(max)

  Any argument of:

• varbinary(max)
• varchar(max)

Exception: If any parameter is an nvarchar of any length, returns nvarchar(max)

Returned Data Type: nvarchar(<= 4000)

  Any argument of:

• nvarchar(<= 4000)

Returned Data Type: varchar(<= 8000)

  Any argument of:

• varchar(<= 8000)

Exception: If any parameter is an nvarchar of any length, returns nvarchar(max)

Implicit conversions follow the rules of conversion. Be aware of the implicit conversion of data and time data. For example:

DECLARE
      @d1 DATETIME = GETDATE()
      ,@d2 DATE
      ,@d3 VARCHAR(10)
      ,@t1 TIME = GETDATE()
;

SET @d2 = @d1;
SET @d3 = CONVERT(VARCHAR(10),@d1,101);
;

SELECT CONCAT('A ',@d1,' | ',@d2,' | ',@d3,' | ',@t1);

Returns:

A Jan 2 2023 5:14AM | 2023-01-02 | 05:14:28 | 05:14:28.7033333

CONCAT_WS

CONCAT_WS takes a first argument as a single character delimiter which is applied between all following parameters. NULLs are ignored. If delimiters indicate no values are needed, ensure that any nulls are replaced with empty strings.

DECLARE
           @last VARCHAR(30) = 'Edison'
       ,@middle VARCHAR(30) = NULL
       ,@first VARCHAR(30) = 'Thomas'
; 

SELECT CONCAT_WS(SPACE(1),@first,@middle,@last);

Returns: Thomas Edison

SELECT CONCAT_WS('|',@first,@middle,@last);

Returns: Thomas|Edison

SELECT CONCAT_WS('|',COALESCE(@first,''),COALESCE(@middle,''),COALESCE(@last,''));

Returns: Thomas||Edison

Need quotes around the text fields as well? Use QUOTENAME

SELECT CONCAT_WS('|'
                ,QUOTENAME(COALESCE(@first,''),'""')
                ,QUOTENAME(COALESCE(@middle,''),'""')
                ,QUOTENAME(COALESCE(@last,''),'""')
          );

Returns: "Thomas"|""|"Edison"

That’s Better

SELECT CONCAT_WS('|'
                ,COALESCE(QUOTENAME(@first,'""'),'')
                ,COALESCE(QUOTENAME(@middle,'""'),'')
                ,COALESCE(QUOTENAME(@last,'""'),'')
          );

Returns: "Thomas"||"Edison"

So which method is best?

All three choices have advantages and drawbacks. If you are working on a version earlier than 2012, CONCAT is not available (and you should really be thinking about an upgrade).

For SQL 2012 – 2016, CONCAT may present cleaner code and has the advantage of handling NULLS and implicit datatype conversions gracefully.

For 2017+, CONCAT_WS may be a cleaner option for handling delimited output.

Bonus: SSIS and the WITH RESULT SETS

Executing a stored procedure using the WITH RESULT SETS to define columns for an SSIS package allows SSIS to see the columns that may be masked by temp tables in the stored procedure. However, it can make the procedure call very wordy and complex.

One way to simplify this is to define the procedure result set as a single column with each row the complete delimited record. UNION can be used to also output a header row. This makes the WITH RESULT SET clause very short.

If including a header row, you must use ORDER BY to guarantee that the header row is the first in the result set.

WITH Outfile AS
(
SELECT
       1 AS RowNum
     ,CONCAT_WS(‘|’,’FirstName’,’MiddleName’,’LastName’) AS Record
UNION ALL
SELECT
       2 AS ROWNUM
       ,CONCAT_WS(‘,’,value1,value2,value3) AS Record
)

SELECT
       Record
FROM
       Outfile
ORDER BY
       RowNum
;

Save Money on Storage

If you’re looking ways to save money on storage, the iatricSystems SQL Server Data Compression Manager tool could reduce your storage footprint by up to 66% or more. By utilizing Microsoft’s data compression for SQL server, our Compression Manager tool automates the process of compressing individual tables and indexes with ALTER commands to save your organization time and money, without any significant impact on performance.

For more information about our SQL Server Data Compression Manager tool, or help with MEDITECH DR optimization, reporting, extracting, index creation or analysis please feel free to give your iatricSystems Account Executive a call or email info@iatric.com to discuss how we can help support your team!

References:
CONCAT (Transact-SQL)
Rules of Conversion
QUOTENAME