Replacing characters in a string based on strings in sql table

Question

I need to replace the list of characters in a string with some displayed characters.

I have a table "dbo.CharacterMappings" with two columns: "CharacterToFilter" and "ReplacementCharacter".

Let's say that there are 3 entries in this table:

Filter Replacement $ s @ a 0 o 

How to replace all filter characters in a string based on these mappings?

i.e. "Hell0 c @t $" should become "Hello cats".

I can't think of any way to do this without resorting to a table variable, and then iterate over it. That is, have a table variable with a column of "count", and then use a loop to select 1 row at a time based on that column. Then I can use the REPLACE function to update characters one at a time.

Edit: I should note that I always want to remove these characters (for example, I don't need to worry about $ 5 → s5).

Answer 1

 declare @s varchar(50)= 'Hell0 c@t $' select @s = REPLACE(@s, CharacterToFilter, ReplacementCharacter) from CharacterMappings select @s 

Answer 2

You can create a function:

 CREATE FUNCTION [dbo].[ReplaceAll] ( @text varchar(8000) ) RETURNS VARCHAR(8000) AS BEGIN SELECT @text = REPLACE(@text,cm.Filter, cm.Replacement) FROM CharacterMappings cm; RETURN @text END 

Then it

 select dbo.[ReplaceAll]('Hell0 c@t $'); 

returns Hello cats