SQL插值字符串

Question

SQL插值字符串

27

T-SQL 支持插值字符串吗？

让我们来看一个例子：

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

我希望能够做到这样：

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

谢谢回答！

- user7867434

在某些情况下，可以使用CONCAT()函数。示例 - tinystone

6个回答

8

为了提高长字符串的可读性，我喜欢这样做：

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

它具有SQL字符串可读性的优势，同时您可以多次使用相同的变量。显然，这不是适当的插值，但我喜欢它比FORMATMESSAGE（）选项更好，因为

它没有长度限制。谁会编写小于2047个字符的查询呢？！ ;)
它支持命名占位符而不仅仅是有序占位符
支持任何版本的SQL Server
您可以多次使用同一个占位符

- Rory

漂亮的格式，这正是我需要的，谢谢。 - cuongle

6

在 T-SQL 中这不是常规做法，但使用 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

- j.f.sebastian

谢谢你的回答！但不幸的是，我发现这个答案有限制。值得一提的是，xp_sprintf仅限于254个字符:(因此，长查询将无法使用它。 - user7867434

抱歉 - 这很烦人。我刚刚发现了另一个可能有用的函数 - FORMATMESSAGE - https://learn.microsoft.com/en-us/sql/t-sql/functions/formatmessage-transact-sql - j.f.sebastian

根据FORMATMESSAGE文档，限制为2047个字符 - 希望这对您的要求足够了！ - j.f.sebastian

FORMATMESSAGE看起来很有前途，但它只支持SQL版本>=2016。我正在使用2008社区版，因此它只能支持内置消息，这意味着将我的自定义字符串作为第一个参数是行不通的。感谢您抽出时间发布这些链接！我可以确认，因为我也安装了SQL 2017服务器和2008。 - user7867434

找到了一个解决办法，虽然有点丑陋。我们需要手动添加自定义消息，然后可以使用自定义消息编号： EXEC sp_addmessage @msgnum=50001, @severity=16, @msgtext=@query, @replace='replace'你介意把它作为另一个答案发布，这样我就可以标记为已接受的答案了吗？非常感谢你的帮助 :) - user7867434

2

我写了两个方便的基于Json的插值函数。一个使用字典样式的Json（键，值对），另一个使用包含每个替换属性的Json对象。

1）数据作为Json对象

例如，给定模板：'嘿，{name}是{age}'，运行：

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

...它返回嘿，Alice今年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) 数据作为Json字典 例如，给定模板：'嘿，{name}今年{age}岁'，运行：

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

... 该函数返回 嘿，Alice 今年 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;

提示：Json需要更多的输入。修改代码并缩短属性名称为“k”（键），“v”（值）和“d”（项），使其更小。然后调用更整洁：

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

注意:

自然地，这只适用于支持Json的MSSQL版本。同时，不支持转义。例如，无法将{a}作为字面量和替换参数。虽然可以使用转义来添加此功能，但由于我没有使用此功能，因此不能保证其效果。

- Rax

2

我喜欢为我的动态 SQL 编写这个函数，因此我编写了一个适用于 SQL Server 2017+ 的函数（使用了 STRING_AGG 和 JSON_VALUE）。它可以被重写以使其与旧版本兼容。

以下是该链接失效时的文本：

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];

- Steven Hibble

0

另外一个选择是，我更倾向于使用伪插值字符串的方法。T-SQL实际上并不支持字符串插值，但我发现用这种方式更容易跟踪事物。它基本上是一个花哨的REPLACE()函数，而不需要一系列嵌套的REPLACE(REPLACE(REPLACE(...))调用。

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);

你可以用字符串字面量、其他变量等替换占位符，而且处理多个替换非常简单，无需跟踪所有嵌套。占位符可以使用任何你想要的内容，但我只是借用了其他语言的风格，因为它与文本的其余部分很好地区分开来。

- Rob Bulmahn

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- user7867434 · Accepted Answer

感谢@j.f.sebastian指出这些解决方案。可惜xp_sprintf仅支持254个字符，因此在使用长查询时并不理想。FORMATMESSAGE限制在2047个字符，所以足以运行长查询。

我将总结所有解决方案并在一篇文章中呈现，以保持组织性。

答案1：

使用FORMATMESSAGE很重要的一点是要知道，如果将插值字符串作为第一个参数，则仅支持SQL版本2012及以上，因此我将发布两个带有FORMATMESSAGE的答案：

SQL版本>= 2012：

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

SQL版本 < 2012：

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

答案2：

使用xp_sprintf存储过程需要注意它的字符限制为254个，所以对于较长的查询不是一个好主意。

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