Oracle - replace string by appearance

Question

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;

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?

Is it possible to number the parameters? I.e., instead of 1 or more times the string {param}, can you use {param1}, {param2} and {param3}? If so, you could try REPLACE(REPLACE(Description, '{param1}', ParamValue1), '{param2}', ParamValue2) — Robert Kock
– Robert Kock, Commented Oct 14, 2019 at 11:44
I wish it could go like that, thing is that on a render of a page, I replace {param} with input control using javascript ... so if I have same name, one replace puts in control everywhere that's needed — Veljko89
– Veljko89, Commented Oct 14, 2019 at 11:57

Bob Jarvis - Слава Україні · Accepted Answer · 2019-10-14 11:47:34Z

1

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 here

answered Oct 14, 2019 at 11:47

Bob Jarvis - Слава Україні

50.2k10 gold badges81 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Oracle - replace string by appearance

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related