11

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

1

5 Answers 5

16

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

SET mycol = replace( replace(mycol, '{1}', 'myoneval'), '{2}', mytwoval)
Sign up to request clarification or add additional context in comments.

2 Comments

Is the second mytwoval missing single quotes?
@SimonTheCat You are right! (Unless mytwoval was a variable)
9

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

2 Comments

Aliasing in WITH clause is supported since Oracle 11gR2.
IMH this is an excellent answer because it addresses the point which was sadly only obliquely referenced in the question, namely allowing the replaced terms to be updated without editing the actual query (which is a useful scenario in a controlled environment).
2

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);

1 Comment

Great solution. Only problem I see here (as in the one above) is that if you have a large data set, say 100000 records, and a fairly big dictionary, say 100, then the intermediate tables will get extremely big like 100000*100 if I understand this thing.... that could easily turn into a major performance issue.
2

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

5 Comments

That's a great use of the WITH clause and kudos for implementing a SQL-only solution, but the result is still pretty tough (for some of us) to parse! It seems to work nicely, but if I implemented in my code, I wouldn't ask a co-worker to review it for correctness. They'd shoot me! :-)
I agree... great solution to a tricky problem using only SQL. But a few hints here and there would be very helpful. I dissected the queries and kind of get it... but only kind of. My coworkers will definitely kill me or praise me or both if I use this in production.
Also a question: do I understand that query correctly, in that it will generate an intermediate table with n*m records where n=number of dictionary elements and m=number of data strings? Because my problem where I could benefit from this has a large number of data strings (in the 10000s... not huge but substantial) and a considerable dict size. Don't want to break the production db.
Hi @jackthehipster, The intermediate result is the number of words within the string to a max of 100 elements per row or string (the max number is given by the size of the pivot table) Probably the best solution for your problem would be to create a procedure that bulk processes all data and returs a plsql table that you can query within SQL using the table function. I would probably not consider a function that processes row by row since context switching could lead to poor performance
an alternative approach would be to use a deterministic function. But the problem is that you need to be able to pass as a parameter every word (instead of the entire string) to cache results, and for this you would need again a cartesian join
-1

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

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.