Replacing characters in a string based on rows in a table sql

Question

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

I have a table 'dbo.CharacterMappings' with 2 columns: 'CharacterToFilter' and 'ReplacementCharacter'.

Say that there are 3 records in this table:

Filter   Replacement    
$        s
@        a
0        o

How would I replace all of the filter characters in a string based on these mappings?

i.e. 'Hell0 c@t$' needs to become 'Hello cats'.

I cant really think of any way of doing this without resorting to a table variable and then looping through it. I.e. have a table variable with a 'count' column then use a loop to select 1 row at a time based on this column. Then I can use the REPLACE function to update the characters one at a time.

Edit: I should note that I always want to strip out these characters (I don't need to worry about $5 -> s5 for example).

Answer 1

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

Answer 2

You could 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 this

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

returns Hello cats