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!
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.
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)
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
sp_addmessage approach has the important drawback that it is not safe when executed concurrently from multiple sessions. This can lead to some pretty confusing messages. The technique is still useful, but care should be taken that it is not used whenever you need interpolation, as that would increase the chances of a clash considerably. Also, from SQL Server 2012 onwards, there is CONCAT to simplify cobbling together strings without having to add CONVERTs everywhere. –
Affright 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'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
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];
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.
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.
© 2022 - 2024 — McMap. All rights reserved.
CONCAT()function can be used. examples – Evacuate