时间戳

首页 > 解决方案 > SQL Server - 按外观替换字符串

sql - SQL Server - 按外观替换字符串

问题描述

有个小问题……这是测试数据

CREATE TABLE #TestReplace (
    Description NVARCHAR(500)
    ,ParamValue1 INT
    ,ParamValue2 INT
    ,ParamValue3 INT
    );

INSERT INTO #TestReplace (Description)
VALUES ('This sentence has no parameteres, and it should be shown like this');

INSERT INTO #TestReplace (
    Description
    ,ParamValue1
    )
VALUES (
    'This sentence has only one parametere, and it should be shown right here {param} with rest of text'
    ,100
    );

INSERT INTO #TestReplace (
    Description
    ,ParamValue1
    ,ParamValue2
    )
VALUES (
    'This sentence has two parameteres, one here {param} and one here {param}, show full sentence'
    ,100
    ,200
    );

INSERT INTO #TestReplace (
    Description
    ,ParamValue1
    ,ParamValue2
    ,ParamValue3
    )
VALUES (
    'This sentence has all parameteres, here {param} and here {param} and there {param}'
    ,100
    ,200
    ,300
    );

在我的句子中，我{param}有时会或从不出现一个单词......和列ParamValue1，......我怎么能用列的值替换第一次出现的单词，用列的值替换第二个单词，用列的值替换第三个单词.. . 我无法将 word 更改为, and and go with simple replaceParamValue2ParamValue3{param}ParamValue1{param}ParamValue2ParamValue3{param}{param1}{param2}{param3}

到目前为止，我设法只替换了第一次出现...

SELECT CASE 
        WHEN CHARINDEX('{param}', DESCRIPTION) > 0
            THEN STUFF(DESCRIPTION, CHARINDEX('{param}', DESCRIPTION), LEN('{param}'), ParamValue1)
        ELSE DESCRIPTION
        END
FROM #TestReplace

这在 Oracle 中很容易管理（Oracle - 按外观替换字符串）

标签： sqlsql-serverreplace

解决方案

您可以使用以下方法将它们链接在一起APPLY：

SELECT COALESCE(v3.DESCRIPTION, v2.DESCRIPTION, v1.DESCRIPTION, tr.DESCRIPTION)
FROM #TestReplace tr CROSS APPLY
     (VALUES (CASE WHEN tr.Description LIKE '%{param}%'
                   THEN STUFF(tr.DESCRIPTION, CHARINDEX('{param}', tr.DESCRIPTION), LEN('{param}'), tr.ParamValue1)
              END)
     ) v1(description) CROSS APPLY
     (VALUES (CASE WHEN v1.Description LIKE '%{param}%'
                   THEN STUFF(v1.DESCRIPTION, CHARINDEX('{param}', v1.DESCRIPTION), LEN('{param}'), tr.ParamValue2)
              END)
     ) v2(description) CROSS APPLY
     (VALUES (CASE WHEN v2.Description LIKE '%{param}%'
                   THEN STUFF(v2.DESCRIPTION, CHARINDEX('{param}', v2.DESCRIPTION), LEN('{param}'), ParamValue3)
              END)
     ) v3(description);

推荐阅读