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

podiluska · Accepted Answer · 2012-10-24 15:05:03Z

12

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

answered Oct 24, 2012 at 15:05

podiluska

51.6k7 gold badges102 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

RobH Over a year ago

Wow, thanks. I didn't know/have never seen REPLACE used like that!

LittleBobbyTables - Au Revoir Over a year ago

That would only work in a function or stored procedure, but it's still pretty slick.

podiluska Over a year ago

@LittleBobbyTables with a couple of semi-colons, I think you could use it from anywhere?

Tim Schmelter · Accepted Answer · 2012-10-24 15:19:16Z

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, 2012 at 15:19

Tim Schmelter

462k79 gold badges721 silver badges981 bronze badges

2 Comments

RobH Over a year ago

The code is going to be part of a function anyway but thanks for pointing it out.

Ravi Vanapalli Over a year ago

Thanks Tim, this helped me build a large text search replace. Saved my time too.

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related