SQL Interpolated Strings

Question

Does T-SQL support Interpolated String?

Let's put up this example:

SET @query = 'SELECT ' + @somevariable + ' FROM SOME_TABLE'

I want to be able to do something like that:

SET @query = 'SELECT {@somevariable} FROM SOME_TABLE'

Thanks for the answers!

Answer 1

To add another option, my preference is for a pseudo-interpolated string approach. T-SQL doesn't actually support string interpolation, but I find it easier to keep track of things this way. It's basically a fancy REPLACE(), without needing some varying number of nested REPLACE(REPLACE(REPLACE(...) calls.

DECLARE @message VARCHAR(MAX) = 'Some {item} with {count} {collection}.';

SELECT @message = REPLACE(@message, SearchText, ReplaceText)
FROM ( VALUES
  ('{item}', 'text string'),
  ('{count}', '3'),
  ('{collection}', 'variables')
) _ (SearchText, ReplaceText);

You can replace the placeholders with string literals, other variables, etc., and it's really easy to handle many replacements without keeping track of all the nesting. You can use whatever you want for the placeholder, but I just borrowed the style from other languages because it stands out well from the rest of the text.

Answer 2

For readability when your strings are long I like doing this:

SET @query = replace( replace( replace( 
               'SELECT {@variable1} FROM {@variable2} WHERE {@variable3}'
               , '{@variable1}', @variable1 )
               , '{@variable2}', @variable2 )
               , '{@variable3}', @variable3 )

It has the advantage of readability of the SQL string plus you can use the same variable multiple times. Clearly it's not proper interpolation but I like it better than the FORMATMESSAGE() option since

• It doesn't have a length limitation. Who writes queries shorter than 2047 characters anyway?! ;)
• It supports named placeholders instead of just ordered ones
• Supported in any version of SQL Server
• You can use the same placeholder multiple times

Answer 3

Wrote 2 handy Json based interpolation functions. One makes use of a dictionary style Json (key, value pairs), the other a Json object containing a property for each substitution.

1) Data as Json object

For example, given the template: 'Hey, {name} is {age}', run:

SELECT [res4] = [ccgen].[fn_string_interpolation_object]('Hi, {name} is {age}' ,'{"name":"Alice", "age":24}')

... which returns Hey, Alice is 24

CREATE OR ALTER FUNCTION [CcGen].[fn_string_interpolation_object](
   @template NVARCHAR(4000), @data_json NVARCHAR(4000)) RETURNS NVARCHAR(4000) AS

   /*
   =============================================
   C# or Java like string interpolation brought to TSQL.

   example - copy to run proc
   -----------------------
   --property names must match those in template. Same as a dynamic object in C#

   SELECT [res4] = [ccgen].[fn_string_interpolation_object]('Hi, {name} is {age}' ,'{"name":"Alice", "age":24}')

   -- returns
   Hi, Alic is 24
   =============================================
   */

BEGIN
   SELECT @template = REPLACE(@template ,'{' + [key] + '}' ,[value]) FROM OPENJSON(@data_json);
   RETURN @template;
END;

2) Data as Json dictionary For example, given the template: 'Hey, {name} is {age}', run:

SELECT [res2] = [ccgen].[fn_string_interpolation]('Hey, {name} is {age}','{"items":[{"key":"name", "value":"Alice"},{"key":"age", "value":"24"}]}')

... which returns Hey, Alice is 24

CREATE OR ALTER FUNCTION [CcGen].[fn_string_interpolation](
   @template NVARCHAR(4000), 
   @key_value_json NVARCHAR(4000)) RETURNS NVARCHAR(4000) AS

   /*
   =============================================
   C# or Java like string interpolation brought to TSQL.
   
   example - copy to run proc
   -----------------------

   DECLARE @json NVARCHAR(2048) = N'{
    "items": [
        {
            "key": "A",
            "value": "a1"
        },
        {
            "key": "B",
            "value": "b2"
        },
        {
            "key": "C",
            "value": "c3"
        }
    ]
}';

   DECLARE @template NVARCHAR(4000) = 'message:= A:{A}, B:{B}, C:{C}'
   select res = ccgen.fn_string_interpolation(@template, @json)

   -- returns
   formatted 3 = A:a1, B:b2, C:c3
   =============================================
   */

BEGIN
   SELECT @template = REPLACE(@template ,'{' + [key] + '}' ,[value]) FROM OPENJSON(@key_value_json ,'$.items') WITH ( [key] VARCHAR(200) '$.key', [value] VARCHAR(4000) '$.value' );
   RETURN @template;
END;

Tip: The Json takes a little more typing. Modify the code and shorten property names to "k" (key) ,"v" (value) and "d" (items) to make it tiny. Then calling is neater:

SELECT [res2] = [ccgen].[fn_string_interpolation]('Hey, {name} is {age}','{"d":[{"k":"name", "v":"Alice"},{"k":"age", "v":"24"}]}')
 

Notes:

Naturally this will only work on MSSQL versions that support Json. Also there is no support for escaping. For example it's not possible to have {a} as both a literal and a substitution param. This could be added using escaping but can't warrant the effort since I have no use for the feature.

Answer 4

I like doing this for my dynamic SQL, so I wrote a function for SQL Server 2017+ (uses STRING_AGG and JSON_VALUE). It could be rewritten to make it compatible with older versions.

Here's the text in case that link dies:

CREATE OR ALTER FUNCTION [Tools].[StringInterpolation] 
 (@Template VARCHAR(MAX)
, @JSON_Row NVARCHAR(MAX))
/*
This function replaces a string template with actual values from a JSON-formatted row
The table returns a single column: FormattedString

** Requires SQL Server 2017+ for STRING_AGG (could be rewritten using XML PATH)
** Requires SQL Server 2016+ for JSON_VALUE (maybe you could use XML)

EXAMPLE: 
SELECT *
FROM (SELECT [Name] = 'Steven', Adjective = 'internet person',        Verb = 'writes helpful(?) SQL functions') [d]
CROSS APPLY Tools.StringInterpolation ('{Name} is a {Adjective} who {Verb}.', (SELECT [d].* FOR JSON PATH))

Name   | Adjective              | Verb                            | FormattedString
-------+------------------------+---------------------------------+-----------------------------------------------------------------
Steven | internet person        | writes helpful(?) SQL functions | Steven is a internet person who writes helpful(?) SQL functions.
*/ 
RETURNS TABLE
  RETURN
  WITH [CTE_10]
       AS (SELECT [Number]
           FROM(VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) ) [v]([Number])),
       -------------------
       /* 100 rows (all 1s) */
       [CTE_100]
       AS (SELECT [Number] = 1
           FROM [CTE_10] [a]
                CROSS JOIN [CTE_10] [b]),
       -------------------
       /* 1,000,000 rows max (all 1s) */
       [CTE_1000000]
       AS (SELECT [Number] = 1
           FROM [CTE_100] [a]
                CROSS JOIN [CTE_100] [b]
                CROSS JOIN [CTE_100] [c]),
       -------------------
       /* Numbers "Table" CTE: 1) TOP has variable parameter = DATALENGTH(@Template), 2) Use ROW_NUMBER */
       [CTE_Numbers]
       AS (SELECT TOP (ISNULL(DATALENGTH(@Template), 0)) 
                  [Number] = ROW_NUMBER() OVER(ORDER BY (SELECT NULL) )
           FROM [CTE_1000000]),
       -------------------

       /* This is tricky. Get each start of each variable or non-variable
          Variables look like {...}
          Non-variables look like }...{ (i.e. the bits between the variables) */
       [CTE_Start]
       AS (SELECT [Type] = 'Text'
                , [Start] = 1
           UNION ALL
           SELECT [Type] = IIF([Char] = '{', 'Variable', 'Text')
                , [Start] = [Number] + 1 -- start *after* the { or }
           FROM [CTE_Numbers]
                CROSS APPLY (SELECT [Char] = SUBSTRING(@Template, [Number], 1)) [c]
           WHERE [Char] IN ( '{', '}' ) ),
       -------------------

       /* Pair each "start" with the next to find indicies of each substring */
       [CTE_StringIndicies]
       AS (SELECT [Type]
                , [Start]
                , [End] = ISNULL(LEAD([Start]) OVER(
                                 ORDER BY [Start]) - 1, DATALENGTH(@Template) + 1)
           FROM [CTE_Start]),
       -------------------

       /* Get each substring */
       [CTE_Variables]
       AS (SELECT [Start]
                , [Type]
                , [SubString] = SUBSTRING(@Template, [Start], [End] - [Start])
           FROM [CTE_StringIndicies]),
       -------------------

       /* If it's a variable, replace it with the actual value from @JSON_Row
          Otherwise, just return the original substring */
       [CTE_Replacements]
       AS (SELECT [Start]
                , [Substring] = IIF([Type] = 'Variable', JSON_VALUE(@JSON_Row, '$[0].' + [Substring]), [Substring])
           FROM [CTE_Variables])
       -------------------

       /* Glue it all back together */
       SELECT [FormattedString] = STRING_AGG([Substring], '') WITHIN GROUP (ORDER BY [Start])
       FROM [CTE_Replacements];

Answer 5

Giving the credits to @j.f.sebastian for pointing out these solutions. Sadly xp_sprintf is limited to 254 characters, so that wouldn't be ideal when using long queries. FORMATMESSAGE instead is limited to 2047 characters, so that's good enough in order to run long queries.

I'll summarize everything in one post for the solutions in order to keep things organized.

Answer 1:

Using FORMATMESSAGE it's important to know, that using interpolated string as first the parameter, Its supported only SQL versions 2012 and above, so I'll post 2 answers with FORMATMESSAGE:

SQL Version >= 2012:

SET @query = FORMATMESSAGE('SELECT %s FROM SOME_TABLE', @somevariable);

SQL Version < 2012:

EXEC sp_addmessage 50001, 16, 'SELECT %s FROM SOME_TABLE', NULL, NULL, 'replace'
SET @query = FORMATMESSAGE(50001, @somevariable)

Answer 2:

Using xp_sprintf stored procedure is important to note that It's limited to 254 characters, so it won't be a good idea for long queries.

DECLARE  @query AS VARCHAR(100)
        ,@somevariable as VARCHAR(10) = '[id]'
EXEC xp_sprintf @query OUTPUT, 'SELECT %s FROM SOME_TABLE', @somevariable

Answer 6

It's not a common way of doing things in T-SQL but it is possible with xp_sprintf

    DECLARE  @query AS VARCHAR(100)
            ,@somevariable as VARCHAR(10) = '[id]'
    EXEC xp_sprintf @query OUTPUT, 'SELECT %s FROM SOME_TABLE', @somevariable

    PRINT @query