PL/SQL Replacing characters in a string using another table

I need to create function that replaces characters in a string to characters from another table. What I’m trying returns exactly the same string from the beginning. Table t_symbols is:

+-------------------+-------------------------+
| Symbol_to_replace | Symbol_in_return_string |
+-------------------+-------------------------+
| K                 | Ќ                       |
| k                 | ќ                       |
| X                 | Ћ                       |
| x                 | ћ                       |
| A                 | Є                       |
| a                 | є                       |
| H                 | Њ                       |
| h                 | њ                       |
| O                 | ¤                       |
| o                 | µ                       |
| U                 | ¦                       |
| u                 | ±                       |
| Y                 | ‡                       |
| y                 | ‰                       |
| I                 | І                       |
| i                 | і                       |
| G                 | Ѓ                       |
| g                 | ѓ                       |
+-------------------+-------------------------+

I need to use cursor and take characters from this table and not just nesting multiple REPLACE

create or replace function f_replace(text in varchar2) return varchar2 is
 ResultText varchar2(2000);
begin
 for cur in (select t.symbol_to_replace, t.symbol_in_return_string from 
t_symbols t) loop
 ResultText:= Replace(text, cur.symbol_to_replace, 
cur.symbol_in_return_string);
end loop;
 return(ResultText);
end f_replace;

Answer

SQL has a function exactly for this. It is not REPLACE (where indeed you would need multiple iterations); it’s the TRANSLATE function.

If the table contents may change, and you need to write a function that looks things up in the table at the time it is called, you could do something like the function I show below.

I am showing a complete example: First I create a table that will store the required substitutions. I only include the first few substitutions, because I want to show how the behavior of the function changes as the table is being modified – without needing to change anything about the function. (Which is the whole point of this.)

Then I show the function definition, and I demonstrate how it works. Then I insert two more rows in the substitutions table and I run exactly the same query; the result will now reflect the longer “list” of substitutions, as needed.

create table character_substitutions ( symbol_to_replace, symbol_in_return_string )
as 
  select  'K', 'Ќ' from dual union all
  select  'k', 'ќ' from dual union all
  select  'X', 'Ћ' from dual union all   
  select  'x', 'ћ' from dual union all
  select  'A', 'Є' from dual union all
  select  'a', 'є' from dual
;

create or replace function my_character_substitutions ( input_str varchar2 )
  return varchar2
  deterministic
as
  symbols_to_replace varchar2(4000);
  symbols_to_return  varchar2(4000);
begin
  select listagg(symbol_to_replace      ) within group (order by rownum),
         listagg(symbol_in_return_string) within group (order by rownum)
  into   symbols_to_replace, symbols_to_return
  from   character_substitutions;

  return translate(input_str, symbols_to_replace, symbols_to_return);
end;
/

select 'Kags' as input_str, my_character_substitutions('Kags') as replaced_str 
from   dual;

INPUT_STR  REPLACED_STR
---------- ------------
Kags       Ќєgs   

OK, so now let’s insert a couple more rows into the table and run the same query. Notice how now the g is also substituted for.

insert into character_substitutions ( symbol_to_replace, symbol_in_return_string )
  select 'G', 'Ѓ' from dual union all
  select 'g', 'ѓ' from dual
;

select 'Kags' as input_str, my_character_substitutions('Kags') as replaced_str
from   dual;

INPUT_STR  REPLACED_STR
---------- ------------
Kags       Ќєѓs