Replacing symbols in string - optimization question

Question

There is a string:

{([ab1]+[ab2])*([bc1]+[bc2])*([cd2]+[cd3])}

And a collection (table of pls_integer index by varchar2(3)):

[ab1] := 1000
[cd3] := 1000
[bc1] := 10000
[cd2] := 10000
[bc2] := 20000
[ab2] := 20000

FYI: This collection is filled via SELECT, so could be used in problem which is described lower:

My goal is to replace symbols in string with an amount from this collection, and get:

'(1000+20000)*(10000+20000)*(10000+1000)'

I have already done this in PL/SQL using loop, then via regexp find first occurence of 3-char symbol, replacing etc. etc.

MY question: is it possible to do it in one query?

Sample select:

SELECT '[ab1]' AS symbol, 1000 AS amt from dual union all
SELECT '[cd3]',1000  from dual union all
SELECT '[bc1]',10000 from dual union all
SELECT '[cd2]',10000 from dual union all
SELECT '[bc2]',20000 from dual union all
SELECT '[ab2]',20000 from dual;

What is your desired result? A string with (1000+20000)*(10000+20000)*(10000+1000)? — Joachim Isaksson

MT0 MT0 · Accepted Answer · 2020-01-20T21:52:10

Use a recursive sub-query factoring clause:

Query:

WITH variables ( id, variable, value ) AS (
  SELECT 1, '[ab1]',  1000 FROM DUAL UNION ALL
  SELECT 2, '[cd3]',  1000 FROM DUAL UNION ALL
  SELECT 3, '[bc1]', 10000 FROM DUAL UNION ALL
  SELECT 4, '[cd2]', 10000 FROM DUAL UNION ALL
  SELECT 5, '[bc2]', 20000 FROM DUAL UNION ALL
  SELECT 6, '[ab2]', 20000 FROM DUAL
),
equations ( equation ) AS (
  SELECT '{([ab1]+[ab2])*([bc1]+[bc2])*([cd2]+[cd3])}' FROM DUAL UNION ALL
  SELECT '{([ab2]+[bc1])}' FROM DUAL
),
substitutions ( equation, id, max_id ) AS (
  SELECT REPLACE( equation, variable, value ),
         v.id,
         v.max_id
  FROM   equations e
         INNER JOIN 
         ( SELECT v.*,
                  MAX( id ) OVER () AS max_id
           FROM   variables v
         ) v
         ON ( v.id = 1 )
UNION ALL
  SELECT REPLACE( equation, variable, value ),
         v.id,
         s.max_id
  FROM   substitutions s
         INNER JOIN
         variables v
         ON ( s.id + 1 = v.id )
)
SELECT equation
FROM   substitutions
WHERE  id = max_id

Output:

| EQUATION                                  |
| :---------------------------------------- |
| {(1000+20000)*(10000+20000)*(10000+1000)} |
| {(20000+10000)}                           |

db<>fiddle here

Replacing symbols in string - optimization question

1 Answers