If you are also a developer like me, maybe your dream was to be able to format and concatenate strings in SQL easily like you can do it some programming language like C#.

In C# for that purpose you can use the String.Format() method. So, lets explain in a simple code snippet how this works in C#.

string person_name = "Damir";
string drink_name = "Gin and tonic";
string s = String.Format("{0} likes {1}. Cheers!", person_name, drink_name);
Console.WriteLine(s);
// Displays: Damir likes Gin and tonic. Cheers!

The definition you can find in MSDN for String.Format is "Converts the value of objects to strings based on the formats specified and inserts them into another string.".

This is so nice because you don't have to be afraid of NULL values. String.Format() will automatically convert NULL value to empty string.

Ok, let's get back to SQL.

To accomplish such a task in SQL you could use some not so elegant solutions. Let's see one approach:

Example:

DECLARE @person_name nvarchar(max) = 'Damir';
DECLARE @drink_name nvarchar(max) = 'Gin and tonic';
SELECT @person_name + ' likes ' + @drink_name + '. Cheers!' AS Result;

The goal is achieved but do you really find this code snippet nice - "@person_name + ' likes ' + @drink_name + '. Cheers!'"?

Let's complicate a little bit and add one more variable of different type:

DECLARE @person_name nvarchar(max) = 'Damir';
DECLARE @drink_name nvarchar(max) = 'Gin and tonic';
DECLARE @number_of_drinks int = 3;
SELECT @person_name + ' will order ' + CAST(@number_of_drinks AS nvarchar(max)) + 'x ' + @drink_name + '. Cheers!' AS Result;

Great success!!! We just use a CAST, few "+" symbols and a various number of some text in quotations.

Also there is a nice probability that one of the variables (columns) could be possibly NULL:

DECLARE @person_name nvarchar(max) = 'Damir';
DECLARE @drink_name nvarchar(max) = NULL;
DECLARE @number_of_drinks int = 3;
SELECT @person_name + ' will order ' + CAST(@number_of_drinks AS nvarchar(max)) + 'x ' + @drink_name + '. Cheers!' AS Result;

Not an issue, to resolve all the problems of this world here is the ultimate solution:

SELECT ISNULL(@person_name, '') + ' will order ' + ISNULL(CAST(@number_of_drinks AS nvarchar(max)), '') + 'x ' + ISNULL(@drink_name, '') + '. Cheers!' AS Result;

The second approach for solving somehow elegantly this problem is with use of older MS SQL function CONCAT or maybe in some cases even the newest one CONCAT_WS. This two are explained more in detail the post MS SQL CONCAT vs CONCAT_WS that you can also find on this blog.

Lucky for us and for every C# developer, MS SQL from version 2016 has an upgraded FORMATMESSAGE function. FORMATMESSAGE  is available from MS SQL 2008 version and it's purpose is to construct a message from strings located in sys.messages.

FORMATMESSAGE statement is enhanced in MS SQL Server 2016 to accept a msg_string argument. Meaning you can use your own string to build a message.

Syntax: FORMATMESSAGE ( { msg_number  | ' msg_string ' } , [ param_value [ ,...n ] ] )

Now, let's solve our case with the fresh updated FORMATMESSAGE function.

Example:

DECLARE @person_name nvarchar(max) = 'Damir';
DECLARE @drink_name nvarchar(max) = 'Gin and tonic';
DECLARE @number_of_drinks int = 3;
SELECT FORMATMESSAGE('%s will order %sx %s. Cheers!', @person_name, CAST(@number_of_drinks AS NVARCHAR(MAX)), @drink_name) AS Result;

I am not sure how this looks to you, but I find it more natural and readable than a previous solution.

Also, if you have a NULL value, you will still have to check the value with ISNULL because the NULL value will be converted into "(null)" string. But not the whole message!

Example:

DECLARE @person_name nvarchar(max) = 'Damir';
DECLARE @drink_name nvarchar(max) = NULL;
DECLARE @number_of_drinks int = 3;
SELECT FORMATMESSAGE('%s will order %sx %s. Cheers!', @person_name, CAST(@number_of_drinks AS NVARCHAR(MAX)), @drink_name) AS Result;

And to conclude, you can also make great select statements from real database tables and format the output using FORMATMESSAGE function.

Example:

USE [WideWorldImporters];
-- Genertate drop statements for all user tables in database
SELECT FORMATMESSAGE('DROP TABLE IF EXISTS [%s].[%s];', SCHEMA_NAME(schema_id), name) AS "Drop Statement" FROM sys.tables WHERE type = 'U';

*MS SQL Server sample database "WideWorldImporters" can be downloaded at: https://github.com/Microsoft/sql-server-samples/releases/tag/wide-world-importers-v1.0

*You can download the complete SQL Script with all examples from the post here: SQL Script.sql (1.98 kb)