How do I concatenate text in a query in SQL Server?

Dealing with data types and conversions

Whenever you plan to concatenate text fields in SQL Server, it's crucial to remember to convert these data types to nvarchar to avert potential errors. The go-to approach for this conversion is CAST() function, especially if you anticipate the concatenated result surpassing the default length. If you're dealing with text fields that don't exceed size limits, just cast to nvarchar(4000):

SELECT CAST(LongTextColumn AS nvarchar(4000)) + ' additional text' AS ConcatenatedText FROM BigTexts;  -- Embrace your inner SQL sherpa, guide your data to safety!


For larger text fields or where the size is dynamic, nvarchar(max) will save the day:

SELECT CAST(VeryLongTextColumn AS nvarchar(max)) + ' appending text' AS ConcatenatedText FROM HugeTexts;  -- It's like a data buffet, there's always room for more!


Invite CONCAT() function or the ISNULL() function to handle null values and replace them with an empty string:

SELECT FirstName + ' ' + ISNULL(MiddleInitial + ' ', '') + LastName AS FullName FROM Employees; -- Don't let NULL values crash the party!


Remember, good performance is the goal, and sometimes concatenation at the application level might save resources and improve performance, especially when dealing with heavy text manipulation.

Different concatenation scenarios

Delimited values using CONCAT_WS

The magic wand here is the CONCAT_WS() function—perfect when you wish to separate values using a chosen delimiter:

SELECT CONCAT_WS(',', Street, City, ZipCode) AS Address FROM Addresses; -- Comma chameleon, addressing made easy!


Handling non-string data types

Non-string data types? No worries! Convert them using CAST() or CONVERT() before combining:

SELECT 'Total: ' + CAST(SumTotal AS nvarchar(20)) AS TotalString FROM InvoiceTotals; -- What's our total? Tune in after this CAST!


Ensuring compatibility with collations

Got multilingual data? Watch out for collations when you concatenate:

SELECT FirstName + ' ' + LastName COLLATE Latin1_General_CI_AS AS FullName FROM InternationalEmployees; -- Different tongues, no problem! We've got you COLLATE'd!


Minding performance

Remember that string concatenation may stretch SQL Server. Test large-scale operations for performance impact and consider batch processing or indexing strategies to avert potential slowdown.

Additional concatenation techniques

Concatenating row values using FOR XML PATH

Concatenating an entire row of values into a string? Try using the FOR XML PATH method:

SELECT STUFF((
    SELECT ', ' + Name FROM Products FOR XML PATH('')
), 1, 2, '') AS ProductNames; -- No need to STUFF yourself, just your queries!


Respecting maximum string sizes

Don't forget about the maximum string size in SQL Server. Chunking the process or storing the result in a text-based data type can avert issues.

Choosing the right concatenation instrument

Dilemma between +, CONCAT, CONCAT_WS, and FOR XML PATH? Evaluate your specific requirements—complexity, null handling, and whether or not to use a delimiter.