Lee Markup takes us through a pair of very useful functions in SQL Server:
SQL Server concatenation methods have been enhanced in modern versions of SQL Server. SQL Server 2012 introduced the CONCAT() function. In SQL Server 2017 we get CONCAT_WS().
A common usage of concatenation, or joining column values together in a string, is combining a FirstName and LastName column into a FullName column. Another common usage might be for creating an address column that pulls together building number, street, city and zip code.
Read on to learn more. CONCAT() and CONCAT_WS() are also extremely helpful for change detection in ETL processes. For example, you might have a queue table to process and only want to update records in which relevant source fields changed, ignoring the ones which don’t exist in your destination. A combination of HASHBYTES() and CONCAT_WS() will do the trick quite nicely.