MS 2014中的字符串修改/过滤

Question

下面是输入字符串：

声明@inputvalue varchar(max)

Set @inputvalue =‘，布鲁塞尔芽，是一种绿色蔬菜，你应该毫无疑问地把它蒸开。蒸腾你的发芽只会使你的免疫力增强。生Sproutbrussels或布鲁塞尔仍具有降胆固醇的能力，但与蒸煮的一样，它更多的是无性的，无味的，无微不至的。同样，以下是蒸brusselssprout的其他一些健康好处“。

我喜欢芽，而不是

Requirement:

如果词“芽”和“布鲁塞尔”不相邻，就需要删除它们。

(1)如果“布鲁塞尔”与“布鲁塞尔”没有相邻，则需要删除“芽”一词(左或右，有或不存在“等分”空间)

(2)如果“芽”没有出现在“芽”旁边，则需要删除“布鲁塞尔”一词(左或右，带或不带等分空间)

(3)即使两者之间有多个空格，这些词语也应视为相邻的。

布鲁塞尔

Brusselssprout

Sproutbrussels

粗体斜体中突出显示的单词将被删除，而Bold中突出显示的单词将不被删除。

注意:整个输入字符串可以是不同的/可能在words.Thus之间根本不包含空格，不能通过使用空格或任何其他分隔符进行拆分。

预期输出字符串：

“Sproutbrussels 是一种绿色蔬菜，你应该毫不怀疑地把它蒸开。蒸只会增强你的免疫力。生的或者仍然有降胆固醇的能力，但是它可以像蒸煮的 sprout一样。同样，这里还有一些蒸brusselssprout".的其他健康好处。我喜欢，不是“

Answer 1

这可能不是很有效，但它起作用了：

Declare @inputvalue varchar(max)

Set @inputvalue = 'Brussels sprout is one type of green veggie you should simply steam away without a doubt. Steaming your sprout will only enable a build up on your immunity. Raw Sproutbrussels or Brussels still has the cholesterol-lowering ability but as much as steamed brussels           sprout. Likewise, here are some of the other health benefits of steaming brusselssprout ".
I like sprout, not Brussels.';

declare @word1 nvarchar(100) = 'brussels';
declare @word2 nvarchar(100) = 'sprout ';

with W1(id, pos, val) as (
    Select 1, CHARINDEX(@word1, @inputvalue, 1)+1, 
        Case When
            right(rtrim(left(@inputvalue, CHARINDEX(@word1, @inputvalue, 1)-1)), len(@word2)) = @word2 
            or left(ltrim(right(@inputvalue, len(@inputvalue) - CHARINDEX(@word1, @inputvalue, 1) - len(@word1) +1)), len(@word2)) = @word2 
        then @inputvalue else 
            left(@inputvalue, CHARINDEX(@word1, @inputvalue, 1) - 1)
        end
    Union All
    Select id+1, CHARINDEX(@word1, val, pos)+1, 
        Case When
            right(rtrim(left(val, CHARINDEX(@word1, val, pos)-1)), len(@word2)) = @word2 
            or left(ltrim(right(val, len(val) - CHARINDEX(@word1, val, pos) - len(@word1) +1)), len(@word2)) = @word2 
        then val else 
            left(val, CHARINDEX(@word1, val, pos) - 1)
            + right(val, len(val) - len(@word1) - CHARINDEX(@word1, val, pos) + 1) 
        end
    From w1     
    Where CHARINDEX(@word1, val, pos) > 0
), W2(id, pos, val) as (
    Select 1, CHARINDEX(@word2, val, 1)+1, 
        Case When
            right(rtrim(left(val, CHARINDEX(@word2, val, 1)-1)), len(@word1)) = @word1 
            or left(ltrim(right(val, len(val) - CHARINDEX(@word2, val, 1) - len(@word2) +1)), len(@word1)) = @word1 
        then val else 
            left(val, CHARINDEX(@word2, val, 1) - 1)
        end
    From ( 
        Select val From w1 Where id in (Select max(id) From w1)
    ) as w
    Union All
    Select id+1, CHARINDEX(@word2, val, pos)+1, 
        Case When
            right(rtrim(left(val, CHARINDEX(@word2, val, pos)-1)), len(@word1)) = @word1 
            or left(ltrim(right(val, len(val) - CHARINDEX(@word2, val, pos) - len(@word2) +1)), len(@word1)) = @word1 
        then val else 
            left(val, CHARINDEX(@word2, val, pos) - 1)
            + right(val, len(val) - len(@word2) - CHARINDEX(@word2, val, pos) + 1) 
        end
    From w2 
    Where CHARINDEX(@word2, val, pos) > 0
)
Select val From w2 Where id in (Select max(id) From w2)

Option (maxrecursion 0)

CTE W1也可以放在一个函数中：

with W1(id, pos, val) as (
    Select 1, CHARINDEX(@word1, @inputvalue, 1)+1, 
        Case When
            right(rtrim(left(@inputvalue, CHARINDEX(@word1, @inputvalue, 1)-1)), len(@word2)) = @word2 
            or left(ltrim(right(@inputvalue, len(@inputvalue) - CHARINDEX(@word1, @inputvalue, 1) - len(@word1) +1)), len(@word2)) = @word2 
        then @inputvalue else 
            left(@inputvalue, CHARINDEX(@word1, @inputvalue, 1) - 1)
        end
    Union All
    Select id+1, CHARINDEX(@word1, val, pos)+1, 
        Case When
            right(rtrim(left(val, CHARINDEX(@word1, val, pos)-1)), len(@word2)) = @word2 
            or left(ltrim(right(val, len(val) - CHARINDEX(@word1, val, pos) - len(@word1) +1)), len(@word2)) = @word2 
        then val else 
            left(val, CHARINDEX(@word1, val, pos) - 1)
            + right(val, len(val) - len(@word1) - CHARINDEX(@word1, val, pos) + 1) 
        end
    From w1     
    Where CHARINDEX(@word1, val, pos) > 0
)
Select val From w1 Where id in (Select max(id) From w1)

然后你只能打两次电话：

Set @newvalue = replaceWords(replaceWords(@inputvalue, 'brussels', 'sprout'), 'sprout', 'brussels')

请注意，我喜欢布鲁塞尔芽菜！)