Pfthb

I need a function that applies a certain rule to replace words in a string.

There are two variables:

• v_imp_user_list - list of users delimited by pipe (example: JOHN|PETER|MARK|USER_PROD)


• v_schema_remap_list - list of users that should be remapped (old value - new value, example: JOHN-GEORGE,USER_PROD-USER_TEST)

The function should parse v_schema_remap_list variable and if the name of the first user (before dash, old value)
exist in v_imp_user_list then replace it with the second user (after dash, new value).

Example:

v_imp_user_list := 'JOHN|PETER|MARK|USER_PROD'

v_schema_remap_list := 'JOHN-GEORGE,USER_PROD-USER_TEST'

Desired outcome:
GEORGE|PETER|MARK|USER_TEST

I have a solution which I will post but for some reason I don't like it and will appreciate any comment/review/better solution.

This function helps me to parse the v_schema_remap_list.

 -- extract nth occurence in a delimited string
 create or replace function f_find_str (
 source_string varchar2
 , occurence_outer number --occurence of the old-new pair
 , occurence_inner number --old/new value, enter 1 for old or 2 for new
 )
 return varchar2
 is
 v_aux varchar2(500);
 v_result varchar2(100);
 begin
 v_aux := ltrim(regexp_substr(',' || source_string, ',[^,]*', 1, occurence_outer),',');
 if occurence_inner = 1 then
 v_result := substr(v_aux, 1, instr(v_aux, '-')-1);
 elsif occurence_inner = 2 then
 v_result := substr(v_aux, instr(v_aux, '-')+1);
 end if;
 return v_result;
 end;
 /

This anonymous block shows my solution (see the loop inside).

 declare
 v_schema_remap_list varchar2(1000) := 'JOHN-GEORGE,USER_PROD-USER_TEST';
 v_imp_user_list varchar2(1000) := 'JOHN|PETER|MARK|USER_PROD';
 v_schema_remap_cnt number := nvl(regexp_count(v_schema_remap_list, '-'), 0); 
 begin

 for i in 1..v_schema_remap_cnt
 loop
 v_imp_user_list := replace(
 v_imp_user_list||'|', 
 f_find_str (
 source_string => v_schema_remap_list
 , occurence_outer => i
 , occurence_inner => 1
 )||'|', 
 f_find_str (
 source_string => v_schema_remap_list
 , occurence_outer => i
 , occurence_inner => 2
 )||'|'
 );
 v_imp_user_list := rtrim(v_imp_user_list, '|');
 end loop;
 dbms_output.put_line(v_imp_user_list); --test output
 end;
 /