主页 / dba / 问题 / 237182
Accepted
youcantryreachingme
Asked: 2019-05-02 22:42:09 +0800 CST

如何用多行数据替换字符串的多个部分？

  • 772

给定一个包含两列的表 - 一个整数 ID 和一个基于文本的字符串 - 我想从一个字符串值开始，该字符串值对包含在花括号中的任意数量的整数进行编码，并与任何其他有效的文本字符混合。

例子：'{1} / {9} ... {12}'

使用单个SELECT语句，我想返回一个字符串，其中所有整数（及其包装括号）都已替换为从我的表中派生的值；具体来说，具有与源字符串中找到的数字相匹配的 ID 的行的文本值......以及大括号外的任何字符保持不变。

这是一个无法完成任务的示例：

select
  replace('{13} {15}','{'+cast(id as varchar)+'}',isNull(display,''))
from testing;

testing这将在表中每行返回 1 行。对于idvalue = 13 的行，字符串的“{13}”部分已成功替换，但“{15}”部分未成功替换（反之亦然，在第 15 行）。

我想创建一个循环遍历所有testing行并反复尝试替换的函数将解决问题。尽管如此，直接的 SQL 语句将比循环更可取。

示例数据

+----+-------------------+
| id |  display          |
+----+-------------------+
|  1 |  Apple            |
|  2 |  Banana           |
|  3 |  Celery           |
|  4 |  Dragonfruit      |
|  5 |  Eggplant         |
|  6 |  Fenugreek        |
|  7 |  Gourd            |
|  8 |  Honeydew         |
|  9 |  Iceberg Lettuce  |
| 10 |  Jackfruit        |
| 11 |  Kale             |
| 12 |  Lemon            |
| 13 |  Mandarin         |
| 14 |  Nectarine        |
| 15 |  Olive            |
+----+-------------------+

示例用例

select replace('{1} {3}',null,null) 
-- Returns 'Apple Celery'

select replace('{3},{4},{5}',null,null); 
-- Returns 'Celery,Dragonfruit,Eggplant'

select replace('{1} / {9} ... {12}',null,null); 
-- Returns 'Apple / Iceberg Lettuce ... Lemon'

显然，replace关键字不起作用。

PS。如果解决方案需要更改字符串的格式以促进这一点，那么这是一种选择。

例如：（'#1 / #9 ... #12'与前面的示例相关）

在这种格式中，也许我们可以将字符串分解为一个行集，基于#，取left字符直到我们找到一个非数字，根据取的数字join到表中，用表的值testing替换#和数字，然后单独替换所有这些将标记修改回单个字符串？testingdisplaystufffor xml path

我正在使用不支持的 SQL Server 2016 string_agg。也就是说，如果有使用 的解决方案string_agg，我仍然有兴趣查看它。

sql-server t-sql

2 个回答

  1. Best Answer

    这是使用递归cte转换变量的示例

    drop table if exists testing;
go
create table testing (id int, display varchar(16));
insert into testing values (1, 'Apple');
insert into testing values (2, 'Banana');
insert into testing values (3, 'Celery');
insert into testing values (4, 'Dragonfruit');
insert into testing values (5, 'Eggplant');

DROP FUNCTION IF EXISTS dbo.TranslateVariables
go
CREATE FUNCTION dbo.TranslateVariables
(
    @StringValue VARCHAR(MAX)
)
RETURNS TABLE

AS
RETURN (

--Common Table Expression for Translation
WITH TranslationTable
AS (
    SELECT FindValue = '{' + convert(varchar(5),id) + '}' ,ReplaceValue = display,ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn
    FROM testing
    )
--Recursive CTE to loop through the TranslationTable and replace FindValue with ReplaceValue
,RecursiveCte as
(
SELECT @StringValue AS StrValue
    ,(
        SELECT count(*)
        FROM TranslationTable
        ) AS cnt

UNION ALL

SELECT replace(StrValue, tt.FindValue, tt.Replacevalue)
    ,cnt - 1
FROM RecursiveCte
JOIN TranslationTable tt
    ON tt.rn = cnt )

SELECT StrValue
    ,cnt
FROM RecursiveCte where cnt = 0
    )
go

    --Verify translation
SELECT *
FROM dbo.TranslateVariables('{1} {3}')
OPTION (MAXRECURSION 32767) -- Don't forget to use the maxrecursion option!

     StrValue     | cnt |
|--------------|-----|
| Apple Celery | 0   |

    SELECT *
FROM dbo.TranslateVariables('{3},{4},{5}')
OPTION (MAXRECURSION 32767) -- Don't forget to use the maxrecursion option!

    | StrValue                    | cnt |
|-----------------------------|-----|
| Celery,Dragonfruit,Eggplant | 0   |
    • 4

  2. 假设您使用的是支持它的版本（并在您原来的 fiddle中使用该版本），您可以使用本机string_split()&string_agg()功能。

    declare @id_list varchar(10) = '1,3'; -- for 'Apple,Celery' 
-- set @id_list = '3,4,5'; --for 'Celery,Dragonfruit,Eggplant'

select string_agg(display, ',') as agg
from (
    select t.display 
    from testing t
    cross apply string_split(@id_list,',') ss 
    where try_cast(ss.[value] as int) = t.id
) x;

    前面的示例假设您在进入的过程中已经放弃了花括号，它只是一个以逗号分隔的数字列表。如果您想在输入的过程中保留花括号，您应该尝试强制它以格式良好的 JSON 格式输入，并使用一些本机 JSON 函数来解析它。上面的关键位是：

    1. 提供一个 s 数组[Id]...
    2. 过滤到只有testing.display你想要的那些，然后......
    3. ...将该文本数组输入到string_agg()

    鉴于您在 2016 年的澄清，并且string_agg()仅在 2017 年可用，您仍然可以string_split()根据需要使用创建数组并使用解决string_agg()您在 OP 中提到的不存在的遗留方法之一。例如：

    select stuff(agg,1,1,'') as agg_trim_first_comma
from (
    select stuff(x.display,1,0,'') 
    from (
        select ',' + t.display 
        from testing t
        cross apply string_split('1,3',',') ss 
        where try_cast(ss.[value] as int) = t.id
    ) x (display )
    for xml path('')
) y (agg);
    • 0

相关问题