SQL-'08:多个 Replace 语句是否是一种不好的做法/是否有另一种编写此查询的方法? [英] SQL-'08: Are multiple Replace statements a bad practice/is there another way to write this query?

问题描述

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

我想知道，因为我是一名 Jr. ETL 工程师，想要养成良好的习惯.

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.

推荐答案

嵌套替换很好，但是随着嵌套级别的增加，代码的可读性会下降.如果我要替换大量字符，我会选择更简洁的方法，例如下表驱动的方法.

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]

这篇关于SQL-'08:多个 Replace 语句是否是一种不好的做法/是否有另一种编写此查询的方法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！