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).

score 12 · Accepted Answer · answered Oct 24 '12 at 15:05

12

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

answered Oct 24 '12 at 15:05

podiluska

50,950
7
98
104

Wow, thanks. I didn't know/have never seen REPLACE used like that! – RobH Oct 24 '12 at 15:10
That would only work in a function or stored procedure, but it's still pretty slick. – LittleBobbyTables - Au Revoir Oct 24 '12 at 15:11
@LittleBobbyTables with a couple of semi-colons, I think you could use it from anywhere? – podiluska Oct 24 '12 at 15:16

score 8 · Answer 2 · answered Oct 24 '12 at 15:19

8

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

answered Oct 24 '12 at 15:19

Tim Schmelter

450,073
74
686
939

The code is going to be part of a function anyway but thanks for pointing it out. – RobH Oct 24 '12 at 15:32
1

Thanks Tim, this helped me build a large text search replace. Saved my time too. – Ravi Vanapalli Jan 06 '14 at 13:00

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

2 Answers2

Linked