Oracle - 按外观替换字符串 [英] Oracle - replace string by appearance

问题描述

我有一个有趣的问题，如果你能分享你的想法......我稍微改变了数据，但结构是相同的

I have an interesting issue here, if you could share your thoughts ... I changed data a bit, but structure is same

create table TestReplace (Description varchar2(500), ParamValue1 number, ParamValue2 number, ParamValue3 number);
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);

COMMIT;

在我的句子中，我有时或从不出现一个词 {param} 和列 ParamValue1、ParamValue2、ParamValue3 ... 我怎么能用 ParamValue1 列的值替换第一次出现的词 {param}，第二个词 {param} 具有列 ParamValue2 的值，第三个具有列 ParamValue3

In my sentence I have occurrences of a word {param} sometimes or never ... and columns ParamValue1, ParamValue2, ParamValue3 ... How could I replace first occurences of a word {param} with value of column ParamValue1, second word {param} with value of column ParamValue2 and third with value of column ParamValue3

我尝试过这样的事情......

I tried something like this ...

select CASE WHEN ParamValue1 IS NULL 
    THEN Description 
   ELSE 
    substr(Description, 1, INSTR(Description,'{param}', 1, 1) - 1) || ParamValue1 ||
        CASE WHEN ParamValue2 IS NULL 
            THEN substr(Description, INSTR(Description,'{param}', 1, 1) + 7, LENGTH(Description) - INSTR(Description,'{param}', 1, 1) + 6)
        WHEN ParamValue2 IS NOT NULL THEN
            substr(Description, INSTR(Description,'{param}', 1, 1) + 7, INSTR(Description,'{param}', 1, 2) + 6 - INSTR(Description,'{param}', 1, 1) + 6) || ParamValue2
       END
    END
   END
from TestReplace

但这并没有让我到任何地方，而且我个人认为这在较大的行集上不会很漂亮/很快

But it's not getting me anywhere, and personally don't think this will be pretty / fast on larger set of rows

那么我怎样才能完成这个文本替换?

So how could I accomplish this text replacement?

推荐答案

使用 REGEXP_REPLACE 的嵌套调用:

Use nested invocations of REGEXP_REPLACE:

SELECT REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(DESCRIPTION, 
                                                    '{param}', PARAMVALUE1, 1, 1),
                                     '{param}', PARAMVALUE2, 1, 1),
                      '{param}', PARAMVALUE3, 1, 1)
  FROM TESTREPLACE

dbfiddle在这里

这篇关于Oracle - 按外观替换字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！