SQL Server 2016 使用表中的单词从字符串中删除单词

发布于 2025-01-11 01:16:00 字数 964 浏览 0 评论 0原文

我正在尝试从字符串列表中删除特定的单词/短语。我创建了一个要排除的单词表。我需要删除字符串中出现的任何单词。我想维护一个排除单词/短语的列表，随着我发现更多我想要排除的单词/短语，该列表会随着时间的推移而增长。

例如，我想排除“爆胎”阶段，但如果“爆胎”一词在没有轮胎的情况下出现，那么我想保留它。

下面我有一些代码接近工作，但由于交叉应用而没有完全排除每一行中的每个单词。由于单词之间有空格，这也不适用于阶段。我不确定交叉应用和 string_split 是否是正确的方法。

请注意：我使用的是 SQL Server 2016，兼容性级别为 130

declare @String table (stringid int, string varchar(100))
declare @WordExclude table (wordexcludeid int, wordexclude varchar(50))

insert into @String values (1, 'The police were called'), (2, 'I noticed ice froze the door 
shut and wind blew the roof off'), (3, 'The car had a blown tyre due to the storm')
insert into @WordExclude values (1,'Police'), (2, 'Noticed'), (3, 'Shut'), (4, 'Blown tyre'), 
(5, 'Storm')

select 
    s.stringid 
    ,value stringwords
    ,replace(s.string, e.wordexclude, '')
from @String s
cross apply string_split(s.string, ' ')
join @WordExclude e
on string like '%'+e.wordexclude+'%'

原文

I am trying to remove specific words/phrases from a list of strings. I have created a table of words to exclude. I need to remove any of these words that appear in the string. I would like to maintain a list of exclusion words/phrases which will grow over time as I discover more words/phrases I want to exclude.

As an example, I want to exclude the phase 'blown tyre', but if the word 'blown' appears without tyre, then I want to keep it.

Below I have some code which is close to working but doesn't fully exclude every word from ever line due to the cross apply. This also does not work with phases due to the spaces inbetween words. I am not sure if the cross apply and string_split is the correct approach to take.

Please note: I am using SQL Server 2016 with compatibility level 130

declare @String table (stringid int, string varchar(100))
declare @WordExclude table (wordexcludeid int, wordexclude varchar(50))

insert into @String values (1, 'The police were called'), (2, 'I noticed ice froze the door 
shut and wind blew the roof off'), (3, 'The car had a blown tyre due to the storm')
insert into @WordExclude values (1,'Police'), (2, 'Noticed'), (3, 'Shut'), (4, 'Blown tyre'), 
(5, 'Storm')

select 
    s.stringid 
    ,value stringwords
    ,replace(s.string, e.wordexclude, '')
from @String s
cross apply string_split(s.string, ' ')
join @WordExclude e
on string like '%'+e.wordexclude+'%'

分享到QQ

分享到微博