Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
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: 2099 | Views in the last 30 days: 1
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...


Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones