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.
>Solution :
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;