SQL replace from list

Question

I'm trying to figure our how I can replace a string using data from another table

I have a table that looks like this:

Id Translation
1  Peter
2  Sandra
3  Olga

Now I want to select all and replace the translations using a list that looks like this:

Original New
e        #
r        ?
lg       *%

So that the select list looks like this:

Id  Translation
1   P#t#?
2   Sand?a
3   O*%a

So, for each translation, I need to have a REPLACE(Translation,Original,New). Or in other words: I need to go through every "Translation" in my first list and make another loop in my replacement table to see what to replace

Bare in mind that the first list has 25'000 rows and the second has 50'000, so I can't just type it by hand :)

EDIT

Just to clarify: The Original and New from my look up table can be both letters and words so the table can looks like this:

Original New
one      two
three    fifty
sun      moon

Answer 1

To do this in one query, you need to use a recursive CTE. Something like:

with trans as (
      select t.original, t.new, row_number() over (order by t.original) as seqnum,
             count(*) over () as cnt
      from translations
     ),
     t as (
      select tt.id, tt.string, replace(tt.string, trans.original, trans.new) as replaced,
             seqnum + 1 as seqnum, cnt
      from totranslate tt join
           trans
           on trans.id = 1
      union all
      select t.id, t.string, replace(t.string, trans.original, trans.new),
             seqnum + 1 as seqnum, cnt
      from t join
           trans
           on t.seqnum = trans.id
      where t.seqnum <= t.cnt
     )
select t.id, t.string, t.replaced
from t
where seqnum = cnt;