Multiple Replace on text string

Transact-SQL
1

Hi

I need to run multiple Replaces (up to 50) on a single column and I am wondering if there is a better solution than what I think I have to do.

SELECT [Part_Number]
, REPLACE(REPLACE(REPLACE(REPLACE([Description],'IND%A'),'WIEL,'GST'),'WIE','GST'),'BLK','B') as 'New_Description'

I am using SQL Server 2008.

2

please provide sample data?

create table #jongee(Descriptionvarchar(50), Part_Number varchar(100) )

insert into #jongee
select 'chicken nuggets', 'EAT MOR CHIKIN'

3

Clearly important that those two replaces happen in THAT order ...

You could have a Key/Value table of subsitutions, and replace them one-by-one, but I expect that would be slow.

When i have nested REPLACE statements I write them like this

REPLACE(REPLACE(REPLACE(REPLACE(
    [Description]
    ,'IND%A')     -- This one has a parameter missing :)
    ,'WIEL','GST')
    ,'WIE','GST')
    ,'BLK','B')

because I think it makes it easy to copy & paste a new substitution, and also to visually read / check what the substitutions are. But that's the only suggestion that I have.

1 Like