SQL Server 2012 introduces a couple of new string functions, one of which is Concat. This function takes two or more strings and merges them to form one string:

DECLARE @String1      VARCHAR(100) = 'Hello there, ';

DECLARE @String2      VARCHAR(100) = 'SQL Server Central.';

SELECT CONCAT(@String1, @String2) AS MergedString;

Here’s the output:

Hello there, SQL Server Central.

Great stuff. But what do you do if you’re running an earlier version of SQL Server? No problem, we can roll our own string concatenation function. We can even add an enhancement to the SQL Server 2012 version, by stating whether two empty strings should return NULL or an empty string.

So, what does this function do?  It accepts two strings and one bit parameter. The strings are concatenated, and the bit determines whether a NULL or empty string should be returned if null or empty strings are passed.

The function begins by checking if either of the strings passed in are NULL. If either string is NULL, it is converted to an empty string. This is done so we can check if both values passed were empty or NULL. If both values are empty, we check the bit value. If this is set to 1, we return a NULL value. If it is 0, we return an empty string.

If, however, the user has passed in at least one non-empty value, we move to the ELSE branch. Because we’ve already converted the strings to empty strings, we don’t need to worry about NULL values. So we have a simple one-liner that joins the strings together.

Here are a few test cases and what they return:

SELECT dbo.Concat(NULL, NULL, 1)          -- Returns NULL

SELECT dbo.Concat(NULL, NULL, 0)          -- Returns NULL

SELECT dbo.Concat(NULL, '', 1)            -- Returns NULL

SELECT dbo.Concat(NULL, '', 0)            -- Returns empty string

SELECT dbo.Concat('', NULL, 1)            -- Returns NULL

SELECT dbo.Concat('', NULL, 0)            -- Returns empty string

-- Returns Hello there, SQL Server Central.

SELECT dbo.Concat('Hello there,', ' SQL Server Central.', 1)     

SELECT dbo.Concat('Hello', NULL, 1)       -- Returns 'Hello'

SELECT dbo.Concat('Hello', NULL, 0)       -- Returns 'Hello'

SELECT dbo.Concat(NULL, 'Hello', 1)       -- Returns 'Hello'

SELECT dbo.Concat(NULL, 'Hello', 0)       -- Returns 'Hello'

One last thing. The SQL Server 2012 CONCAT function allows you to pass in multiple strings, e.g.:

DECLARE @String1      VARCHAR(100) = 'Hello there, ';

DECLARE @String2      VARCHAR(100) = 'SQL Server Central.';

DECLARE @String3      VARCHAR(100) = ' From Mike McQuillan.'

SELECT CONCAT(@String1, @String2, @String3) AS MergedString;

This returns:

We can do this by embedding a call to our CONCAT function as one of the string parameters, e.g.:

-- Returns 'Hello there, SQL Server. From Mike McQuillan

SELECT dbo.Concat('Hello there, ', dbo.Concat('SQL Server Central. ', 'From Mike McQuillan', 1), 1)

Here is the evidence:

And there you have it. CONCAT for those of us who can’t use the latest and greatest version of SQL Server. Enjoy!