如何用T-SQL中表的替换值替换字符串中的所有键字段？ [英] How can I replace all key fields in a string with replacement values from a table in T-SQL?

问题描述

我有一个像这样的表：

 TemplateBody
 ---------------------------------------------------------------------
 1.This is To inform #FirstName# about the issues regarding #Location#

$的问题b $ b

这里的键字符串是 #FirstName＃和 #Location＃，由哈希标签来区分。

Here the key strings are #FirstName# and #Location# which are distinguished by hash tags.

我还有另一个表的替换值：

I have another table with the replacement values:

Variables     | TemplateValues
-----------------------------
1.#FirstName# | Joseph William
2.#Location#  | Alaska

我需要用第一个表中的值替换这两个键字符串。

I need to replace these two key strings with their values in the first table.

推荐答案

有几种方法可以完成此操作。我将列出两种方式。每个人都有优点和缺点。我会亲自使用第一个（Dynamic SQL）。

There are several ways this can be done. I'll list two ways. Each one has advantages and disadvantages. I would personally use the first one (Dynamic SQL).

1。动态SQL

• 优点：速度快，不需要递归


• 缺点：无法用于更新表变量

2。递归CTE

• 优点：允许更新表变量


• 缺点：需要递归并且递归CTE速度很慢

1.A。动态SQL：常规表和临时表。

此示例使用临时表作为文本源：

This example uses a temporary table as the text source:

CREATE TABLE #tt_text(templatebody VARCHAR(MAX));
INSERT INTO #tt_text(templatebody)VALUES
    ('This is to inform #first_name# about the issues regarding #location#');

CREATE TABLE #tt_repl(variable VARCHAR(256),template_value VARCHAR(8000));
INSERT INTO #tt_repl(variable,template_value)VALUES
    ('#first_name#','Joseph William'),
    ('#location#','Alaska');

DECLARE @rep_call NVARCHAR(MAX)='templatebody';
SELECT
    @rep_call='REPLACE('+@rep_call+','''+REPLACE(variable,'''','''''')+''','''+REPLACE(template_value,'''','''''')+''')'
FROM
    #tt_repl;

DECLARE @stmt NVARCHAR(MAX)='SELECT '+@rep_call+' FROM #tt_text';
EXEC sp_executesql @stmt;

/* Use these statements if you want to UPDATE the source rather than SELECT from it
DECLARE @stmt NVARCHAR(MAX)='UPDATE #tt_text SET templatebody='+@rep_call;
EXEC sp_executesql @stmt;
SELECT * FROM #tt_text;*/

DROP TABLE #tt_repl;
DROP TABLE #tt_text;

---

1.B。动态SQL：表变量。

需要将表定义为特定的表类型。类型定义示例：

Requires to have the table defined as a specific table type. Example type definition:

CREATE TYPE dbo.TEXT_TABLE AS TABLE(
    id INT IDENTITY(1,1) PRIMARY KEY,
    templatebody VARCHAR(MAX)
);
GO

定义此类型的表变量，并在Dynamic SQL语句中使用如下。注意，不可能用这种方式更新表变量。

Define a table variable of this type, and use it in a Dynamic SQL statement as follows. Note that updating a table variable this way is not possible.

DECLARE @tt_text dbo.TEXT_TABLE;
INSERT INTO @tt_text(templatebody)VALUES
    ('This is to inform #first_name# about the issues regarding #location#');

DECLARE @tt_repl TABLE(id INT IDENTITY(1,1),variable VARCHAR(256),template_value VARCHAR(8000));
INSERT INTO @tt_repl(variable,template_value)VALUES
    ('#first_name#','Joseph William'),
    ('#location#','Alaska');

DECLARE @rep_call NVARCHAR(MAX)='templatebody';
SELECT
    @rep_call='REPLACE('+@rep_call+','''+REPLACE(variable,'''','''''')+''','''+REPLACE(template_value,'''','''''')+''')'
FROM
    @tt_repl;

DECLARE @stmt NVARCHAR(MAX)='SELECT '+@rep_call+' FROM @tt_text';
EXEC sp_executesql @stmt,N'@tt_text TEXT_TABLE READONLY',@tt_text;

---

2。递归CTE：

使用递归CTE编写此代码的唯一原因是您打算更新表变量，否则就不允许这样做。以某种方式使用动态SQL（例如公司政策？）。

The only reasons why you would write this using a recursive CTE is that you intend to update a table variable, or you are not allowed to use Dynamic SQL somehow (eg company policy?).

请注意，默认最大递归级别为100。如果替换变量超过100，则应通过添加 OPTION（MAXRECURSION 32767）在查询末尾（请参见查询提示- MAXRECURSION ）。

Note that the default maximum recursion level is 100. If you have more than a 100 replacement variables you should increase this level by adding OPTION(MAXRECURSION 32767) at the end of the query (see Query Hints - MAXRECURSION).

DECLARE @tt_text TABLE(id INT IDENTITY(1,1),templatebody VARCHAR(MAX));
INSERT INTO @tt_text(templatebody)VALUES
    ('This is to inform #first_name# about the issues regarding #location#');

DECLARE @tt_repl TABLE(id INT IDENTITY(1,1),variable VARCHAR(256),template_value VARCHAR(8000));
INSERT INTO @tt_repl(variable,template_value)VALUES
    ('#first_name#','Joseph William'),
    ('#location#','Alaska');

;WITH cte AS (
    SELECT
        t.id,
        l=1,
        templatebody=REPLACE(t.templatebody,r.variable,r.template_value)
    FROM
        @tt_text AS t
        INNER JOIN @tt_repl AS r ON r.id=1
    UNION ALL
    SELECT
        t.id,
        l=l+1,
        templatebody=REPLACE(t.templatebody,r.variable,r.template_value)
    FROM
        cte AS t
        INNER JOIN @tt_repl AS r ON r.id=t.l+1
)
UPDATE
    @tt_text
SET
    templatebody=cte.templatebody
FROM
    @tt_text AS t
    INNER JOIN cte ON 
        cte.id=t.id
WHERE
    cte.l=(SELECT MAX(id) FROM @tt_repl);

/* -- if instead you wanted to select the replaced strings, comment out 
   -- the above UPDATE statement, and uncomment this SELECT statement:
SELECT 
    templatebody 
FROM 
    cte 
WHERE 
    l=(SELECT MAX(id) FROM @tt_repl);*/

SELECT*FROM @tt_text;

这篇关于如何用T-SQL中表的替换值替换字符串中的所有键字段？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！