Oracle String Replace With Multiple Values

Question

I have one key/value comma separated string, and another keys-labels comma separated string as per the following example (ID/Name/Age):

key/value:    1=101,2=John,3=43

keys labels:  ID,Name,Age

Result:       ID=101,Name=John,Age=43

Is there a built in Oracle function (ex:regexp_replace) that can accomplish this?

score 0 · Answer 1 · answered Sep 15 '22 at 14:53

I don't think there's a simple function, no. regexp_replace only replaces one value at a time, so to replace 3 values in a string, you need to loop over it 3 times.

It's possible to split the strings, use a connect by loop to replace each value, and then put the pairs back together, but it is a bit awkward. Example:

with t as (select '1=101,2=John,3=43' as pairs, 'ID,Name,Age' as labels from dual)
select
    listagg(
    regexp_replace(regexp_substr(pairs, '[^,]+', 1, level),
                   '\d+(.+)',
                   regexp_substr(labels, '[^,]+', 1, level) || '\1')
    ,',') 
from t
connect by regexp_substr(pairs, '[^,]+', 1, level) is not null

See the link above for how to modify this query for a table with multiple rows and a unique key ID.

score 0 · Answer 2 · answered Sep 15 '22 at 14:56

if the key values and labels length are fixed then you can get the result with replace function itself

select REPLACE(REPLACE( REPLACE('1=101,2=John,3=43', '1=','ID='),'2=','Name=') ,'3=','Age=') as finresult from dual ;

OUTPUT:

FINRESULT
ID=101,Name=John,Age=43

Oracle String Replace With Multiple Values

2 Answers2