CODEWITHSUNDEEP
oracle-databasestringreplace
RBS
RBS

Reputation: 3851

how to replace multiple strings together in Oracle

I have a string coming from a table like "can no pay{1},as your payment{2}due on {3}". I want to replace {1} with some value , {2} with some value and {3} with some value .

Is it Possible to replace all 3 in one replace function ? or is there any way I can directly write query and get replaced value ? I want to replace these strings in Oracle stored procedure the original string is coming from one of my table I am just doing select on that table

and then I want to replace {1},{2},{3} values from that string to the other value that I have from another table

Upvotes: 11

Views: 40285

Answers (5)

Ricardo Arnold
Ricardo Arnold

Reputation: 913

If the number of values to replace is too big or you need to be able to easily maintain it, you could also split the string, use a dictionary table and finally aggregate the results

In the example below I'm assuming that the words in your string are separated with blankspaces and the wordcount in the string will not be bigger than 100 (pivot table cardinality)

    with Dict as
     (select '{1}' String, 'myfirstval' Repl from dual
       union all
      select '{2}' String, 'mysecondval' Repl from dual
       union all
      select '{3}' String, 'mythirdval' Repl from dual
       union all  
      select '{Nth}' String, 'myNthval' Repl from dual  
      
     )
    ,MyStrings as
     (select 'This  is the first example {1} ' Str, 1 strnum from dual
      union all
      select 'In the Second example all values are shown {1} {2} {3} {Nth} ', 2  from dual
      union all
      select '{3} Is the value for the third', 3 from dual
      union all
      select '{Nth} Is the value for the Nth', 4 from dual  
      )
    -- pivot is used to split the stings from MyStrings. We use a cartesian join for this
    ,pivot as (
      Select Rownum Pnum
      From dual
      Connect By Rownum <= 100   
      )
    -- StrtoRow is basically a cartesian join between MyStings and Pivot. 
-- There as many rows as individual string elements in the Mystring Table
-- (Max = Numnber of rows Mystring table * 100). 
    ,StrtoRow as
    (
    SELECT rownum rn
          ,ms.strnum
          ,REGEXP_SUBSTR (Str,'[^ ]+',1,pv.pnum) TXT
      FROM MyStrings ms
          ,pivot pv
    where REGEXP_SUBSTR (Str,'[^ ]+',1,pv.pnum) is not null
    )
    -- This is the main Select. 
    -- With the listagg function we group the string together in lines using the key strnum (group by)
   -- The NVL gets the translations: 
       -- if there is a Repl (Replacement from the dict table) then provide it, 
       -- Otherwise TXT (string without translation)
    Select Listagg(NVL(Repl,TXT),' ') within group (order by rn) 
    from
    (
    -- outher join between strings and the translations (not all strings have translations)
    Select sr.TXT, d.Repl, sr.strnum, sr.rn
      from StrtoRow sr
          ,dict d
     where sr.TXT = d.String(+) 
    order by strnum, rn 
    ) group by strnum

Upvotes: 2

Olc
Olc

Reputation: 69

Let's write the same sample as a CTE only:

with fg_rulez as (
  select 1 id,'<' symbol, 'less than' text from dual
  union all select 2, '>', 'greater than' from dual
   union all select 3, '$', 'dollars' from dual
  union all select 4, '+', 'and' from dual
),  fg_Data AS (
   SELECT 'amount $ must be < 1 + > 2' str FROM dual
   union all
   SELECT 'John is > Peter + has many $' str FROM dual
   union all
   SELECT 'Eliana is < mary + do not has many $' str FROM dual
), q(str, id) as (
  SELECT str, 0 id 
  FROM fg_Data 
     UNION ALL
  SELECT replace(q.str,symbol,text), fg_rulez.id
  FROM q 
  JOIN fg_rulez 
    ON q.id = fg_rulez.id - 1
)
SELECT str from q where id = (select max(id) from fg_rulez);

Upvotes: 2

Florin Ghita
Florin Ghita

Reputation: 17643

If there are many variables to replace and you have them in another table and if the number of variables is variable you can use a recursive CTE to replace them. An example below. In table fg_rulez you put the strings with their replacement. In table fg_data you have your input strings.

set define off;
drop table fg_rulez
create table fg_rulez as 
  select 1 id,'<' symbol, 'less than' text from dual
  union all select 2, '>', 'great than' from dual
  union all select 3, '$', 'dollars' from dual
  union all select 4, '&', 'and' from dual;
drop table fg_data;
create table fg_Data AS(
   SELECT 'amount $ must be < 1 & > 2' str FROM dual
   union all
   SELECT 'John is >  Peter & has many $' str FROM dual
   union all
   SELECT 'Eliana is < mary & do not has many $' str FROM dual

   );


WITH  q(str, id) as (
  SELECT str, 0 id 
  FROM fg_Data 
     UNION ALL
  SELECT replace(q.str,symbol,text), fg_rulez.id
  FROM q 
  JOIN fg_rulez 
    ON q.id = fg_rulez.id - 1
)
SELECT str from q where id = (select max(id) from fg_rulez);

So, a single replace.

Result:

amount dollars must be less than 1 and great than 2 
John is great than Peter and has many dollars 
Eliana is less than mary and do not  has many dollars

The terminology symbol instead of variable comes from this duplicated question.

Oracle 11gR2

Upvotes: 9

hamishmcn
hamishmcn

Reputation: 7981

Although it is not one call, you can nest the replace() calls:

SET mycol = replace( replace(mycol, '{1}', 'myoneval'), '{2}', mytwoval)

Upvotes: 16

Mitchel Sellers
Mitchel Sellers

Reputation: 63136

If you are doing this inside of a select, you can just piece it together, if your replacement values are columns, using string concatenation.

Upvotes: -1

Related Questions