sql server 2008 - SQL-'08: Are multiple Replace statements a bad practice/is there another way to write this query?

Question

Select 
Distinct 
    REPLACE(REPLACE(REPLACE(REPLACE(Category, ' & ', '-'), '/', '-'), ', ', '-'), ' ', '-') AS Department 
From 
     Inv WITH(NOLOCK) 

I was wondering because I am a Jr. ETL Engineer and want to develop good habits.

Obviously this could get even longer in many circumstancials.

Answer 1

The nested replace is fine, but as the nesting level increases the readability of your code goes down. If I had a large number of characters to replace I would opt for something cleaner like the below table driven approach.

    declare @Category varchar(25)
    set @Category = 'ABC & DEF/GHI, LMN OP'
    -- nested replace
    select replace(replace(replace(replace(@Category, ' & ', '-'), '/', '-'), ', ', '-'), ' ', '-') as Department 

    -- table driven
    declare @t table (ReplaceThis varchar(10), WithThis varchar(10))
    insert into @t
        values  (' & ', '-'), 
                ('/', '-'),
                (', ', '-'),
                (' ', '-')

    select  @Category = replace(@Category, ReplaceThis, isnull(WithThis, ''))                       
    from    @t
    where   charindex(ReplaceThis, @Category) > 0;

    select @Category [Department]