Hello, everyone.
I think it's impossible, but still maybe there would be some thoughts.
Well, the question is:
I have a sentence.
Is it possible to replace particular words with the others?
E.g. sentence 'I want milk'
What I want is: to replace 'I' with 'He',
'want' with 'wants' and 'milk' with 'sugar'.
The main idea is to use only one level of nesting in "replace" function.
I have an example, the first column is some kinda "prototype". It has the logic which I want to implement, but it's not working properly.
The two other columns are working variants, but they are not what I want.
SQL> with t as (select 'I want milk' str from dual)
2 --
3 select regexp_replace(str, '(I|want|milk)', decode('\1', 'I', 'He', 'want', 'wants', 'milk', 'sugar', '\1')) smth_like_this,
4 replace(replace(replace(str,'I', 'He'),'want', 'wants'), 'milk', 'sugar') what_is_needed,
5 (select str from dual
6 model
7 reference r
8 on (select 1 rn, 'I' from_str, 'He' to_str from dual union all
9 select 2, 'want', 'wants' from dual union all
10 select 3, 'milk', 'sugar' from dual)
11 dimension by (rn)
12 measures (from_str f, to_str t)
13 main m
14 dimension by (0 dim)
15 measures (cast(str AS VARCHAR2(4000)) str)
16 rules iterate (1000) until (presentv(f[iteration_number+1],1,0)=0)
17 (str[0] = replace(str[0], r.f[iteration_number+1], r.t[iteration_number+1]))
18 ) from t
19 /
SMTH_LIKE_THIS WHAT_IS_NEEDED (SELECTSTRFROMDUALMODELREFEREN
-------------- -------------- --------------------------------------------------------------------------------
I want milk He wants sugar He wants sugar