How to replace letters of work with reference table column-CodePudding

I want to replace each letter of a column (nvarchar) with the letters defined earlier:

I got 2 tables : Data, EncodingReference

In data table I have a single column called Name with the data below:

Name
New
My
Beep

In the EncodingReference table I have 2 columns

SourceLetter	TargetLetter
N	E
B	M

What I want to get is the following result set:

Name
EEW
My
Meep

Basically I want to replace each letter with the target letter stored in another table.

CodePudding user response：

Possibly the following may serve your purpose using translate

select Translate(name, s, t) Name
from data d
cross apply (
  select String_Agg(sourceLetter,'')s, String_Agg(Targetletter,'')t
  from EncodingReference
)er;