String.Format like functionality in T-SQL?

Question

If you are using SQL Server 2012 and above, you can use FORMATMESSAGE. eg.

DECLARE @s NVARCHAR(50) = 'World';
DECLARE @d INT = 123;
SELECT FORMATMESSAGE('Hello %s, %d', @s, @d)
-- RETURNS 'Hello World, 123'

---

More examples from MSDN: FORMATMESSAGE

SELECT FORMATMESSAGE('Signed int %i, %d %i, %d, %+i, %+d, %+i, %+d', 5, -5, 50, -50, -11, -11, 11, 11);
SELECT FORMATMESSAGE('Signed int with leading zero %020i', 5);
SELECT FORMATMESSAGE('Signed int with leading zero 0 %020i', -55);
SELECT FORMATMESSAGE('Unsigned int %u, %u', 50, -50);
SELECT FORMATMESSAGE('Unsigned octal %o, %o', 50, -50);
SELECT FORMATMESSAGE('Unsigned hexadecimal %x, %X, %X, %X, %x', 11, 11, -11, 50, -50);
SELECT FORMATMESSAGE('Unsigned octal with prefix: %#o, %#o', 50, -50);
SELECT FORMATMESSAGE('Unsigned hexadecimal with prefix: %#x, %#X, %#X, %X, %x', 11, 11, -11, 50, -50);
SELECT FORMATMESSAGE('Hello %s!', 'TEST');
SELECT FORMATMESSAGE('Hello %20s!', 'TEST');
SELECT FORMATMESSAGE('Hello %-20s!', 'TEST');
SELECT FORMATMESSAGE('Hello %20s!', 'TEST');

NOTES:

• Undocumented in 2012
• Limited to 2044 characters
• To escape the % sign, you need to double it.
• If you are logging errors in extended events, calling FORMATMESSAGE comes up as a (harmless) error

take a look at xp_sprintf. example below.

DECLARE @ret_string varchar (255)
EXEC xp_sprintf @ret_string OUTPUT, 
    'INSERT INTO %s VALUES (%s, %s)', 'table1', '1', '2'
PRINT @ret_string

Result looks like this:

INSERT INTO table1 VALUES (1, 2)

---

Just found an issue with the max size (255 char limit) of the string with this so there is an alternative function you can use:

create function dbo.fnSprintf (@s varchar(MAX), 
                @params varchar(MAX), @separator char(1) = ',')
returns varchar(MAX)
as
begin
declare @p varchar(MAX)
declare @paramlen int

set @params = @params + @separator
set @paramlen = len(@params)
while not @params = ''
begin
    set @p = left(@params+@separator, charindex(@separator, @params)-1)
    set @s = STUFF(@s, charindex('%s', @s), 2, @p)
    set @params = substring(@params, len(@p)+2, @paramlen)
end
return @s
end

To get the same result as above you call the function as follows:

print dbo.fnSprintf('INSERT INTO %s VALUES (%s, %s)', 'table1,1,2', default)

I have created a user defined function to mimic the string.format functionality. You can use it.

stringformat-in-sql

UPDATE:
This version allows the user to change the delimitter.

-- DROP function will loose the security settings.
IF object_id('[dbo].[svfn_FormatString]') IS NOT NULL
    DROP FUNCTION [dbo].[svfn_FormatString]
GO

CREATE FUNCTION [dbo].[svfn_FormatString]
(
    @Format NVARCHAR(4000),
    @Parameters NVARCHAR(4000),
    @Delimiter CHAR(1) = ','
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    /*
        Name: [dbo].[svfn_FormatString]
        Creation Date: 12/18/2020

        Purpose: Returns the formatted string (Just like in C-Sharp)

        Input Parameters:   @Format         = The string to be Formatted
                            @Parameters     = The comma separated list of parameters
                            @Delimiter      = The delimitter to be used in the formatting process

        Format:             @Format         = N'Hi {0}, Welcome to our site {1}. Thank you {0}'
                            @Parameters     = N'Karthik,google.com'
                            @Delimiter      = ','           
        Examples:
            SELECT dbo.svfn_FormatString(N'Hi {0}, Welcome to our site {1}. Thank you {0}', N'Karthik,google.com', default)
            SELECT dbo.svfn_FormatString(N'Hi {0}, Welcome to our site {1}. Thank you {0}', N'Karthik;google.com', ';')
    */
    DECLARE @Message NVARCHAR(400)
    DECLARE @ParamTable TABLE ( Id INT IDENTITY(0,1), Paramter VARCHAR(1000))

    SELECT @Message = @Format

    ;WITH CTE (StartPos, EndPos) AS
    (
        SELECT 1, CHARINDEX(@Delimiter, @Parameters)
        UNION ALL
        SELECT EndPos + (LEN(@Delimiter)), CHARINDEX(@Delimiter, @Parameters, EndPos + (LEN(@Delimiter)))
        FROM CTE
        WHERE EndPos > 0
    )

    INSERT INTO @ParamTable ( Paramter )
    SELECT
        [Id] = SUBSTRING(@Parameters, StartPos, CASE WHEN EndPos > 0 THEN EndPos - StartPos ELSE 4000 END )
    FROM CTE

    UPDATE @ParamTable 
    SET 
        @Message = REPLACE(@Message, '{'+ CONVERT(VARCHAR, Id) + '}', Paramter )

    RETURN @Message
END

There is a way, but it has its limitations. You can use the FORMATMESSAGE() function. It allows you to format a string using formatting similar to the printf() function in C.

However, the biggest limitation is that it will only work with messages in the sys.messages table. Here's an article about it: microsoft_library_ms186788

It's kind of a shame there isn't an easier way to do this, because there are times when you want to format a string/varchar in the database. Hopefully you are only looking to format a string in a standard way and can use the sys.messages table.

Coincidentally, you could also use the RAISERROR() function with a very low severity, the documentation for raiseerror even mentions doing this, but the results are only printed. So you wouldn't be able to do anything with the resulting value (from what I understand).

Good luck!