Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

A CONCAT Function For The Rest Of Us

By Mike McQuillan,

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!

Total article views: 2101 | Views in the last 30 days: 0
Related Articles

same string selection

same string selection


String search

return numeric value in a string


MDX #23 – “Hello World!” Lesson in MDX

Almost every tool we learned has some sort of “Hello World!” tutorial lesson. So here comes the “He...


string Comparison.

'string' = 'string___' ???


TSQL Challenge 55 - Multiply two very long decimal strings and return

This challenge to multiply two positive integer strings and return their product. The strings can be...