CODEWITHSUNDEEP
regexoracle-databaseoracle11g
Utami Kusumadewi
Utami Kusumadewi

Reputation: 11

REGEXP_REPLACE how to using multiple replacement_string

how to multiple replacement string using regexp_replace in oracle?

formula = 1*3

formula detail (1->value1, 2->value3, 3->value3)

I want the result

formula = value1*value3

Upvotes: 1

Views: 451

Answers (1)

Littlefoot
Littlefoot

Reputation: 142713

I don't understand what this:

formula = 1*3

formula detail (1->value1, 2->value3, 3->value3)

actually represents. What is the first row? Is it a string, stored in some table? Is it a string stored in a variable? Is it the complete string, or is the string just 1*3 (without formula =)?

What is the second row? The same doubts as for the first one.

Anyway: if we pretend that the first row represents a string, while the second represents your wish and not some code, then nested REPLACE (i.e. no regular expressions at all) does the job:

SQL> create or replace function f_rep
  2    (par_1 in varchar2, par_2 in varchar2, par_3 in varchar2)
  3  return varchar2
  4  is
  5    l_str varchar2(200) := 'formula = 1*3';
  6  begin
  7    l_str := replace(replace(replace(l_str, '1', par_1),
  8                                            '2', par_2),
  9                                            '3', par_3);
 10    return l_str;
 11  end;
 12  /

Function created.

SQL> select f_rep('value1', null, 'value3') result from dual;

RESULT
--------------------------------------------------------------------------------
formula = value1*value3

SQL>

Upvotes: 1

Related Questions