How to put a values in assigned space inside a string from other table-CodePudding

I've two tables. Name of first is, let's say, Table_A:

A_ID	Text
1	My name is {0}, and surname {1}
2	I live in [city:0]
...	...

Second one is Table_B:

B_ID	Number	Value
1	0	John
1	1	Smith
2	0	LA
...	...

Column A_ID in Table_A is unique, but B_ID is not. Also, B_ID is foreign key. Additionally, parameters such as {0} or {1} might be different, i.e [str:0], [number:1] etc.

And task is: to write a PLSQL function which changes parameters in Table_A with the values of Table_B.

So, result should be:

A_ID	Text
1	My name is John, and surname Smith
2	I live in LA
...	...

P.S. the solution for the problem would be better, but any directions in which I should move on also will be a plus.

CodePudding user response：

Here is one very basic solution. Add more substitution values as needed - one additional outer join per "number" is needed. Suppose you support up to 20 "number" values then you'll need 20 outer joins.

WITH table_a (a_id, text) AS
(SELECT 1, 'My name is {0}, and surname {1}' FROM DUAL UNION ALL
 SELECT 2, 'I live in [city:0]' FROM DUAL 
),
table_b (b_id, a_id, nr, val) AS
(
 SELECT 1, 1, 0, 'John' FROM DUAL UNION ALL
 SELECT 2, 1, 1, 'Smith' FROM DUAL UNION ALL
 SELECT 3, 2, 0, 'LA' FROM DUAL 
)
SELECT --a.a_id, 
       REPLACE(
       REPLACE(
       REGEXP_REPLACE(text,'([[[:alpha:]] :)([[:digit:]])(])','{\2}')
       ,'{0}',b0.val)
       ,'{1}',b1.val) as parsed_val
  FROM table_a a
       LEFT OUTER JOIN table_b b0 ON a.a_id = b0.a_id AND b0.nr = 0
       LEFT OUTER JOIN table_b b1 ON a.a_id = b1.a_id AND b1.nr = 1;

PARSED_VAL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
----------------------------------
My name is John, and surname Smith
I live in LA

The REGEXP_REPLACE statement replaces strings like city:0], [name:1] with {0} and {1} so only those strings need to be substituted.