通过将文本与一组字符进行比较来替换关键词

Question

我有一个在SQL表中的Nvarchar列。我想通过将其与一组预定义字符进行比较，以***替换/掩盖其中一些单词。这意味着如果我的列包含预定义单词列表中的单词，则要对它们进行掩盖。预定义单词列表大约有400个单词。

示例DDL和DML：

DECLARE @Comments TABLE (id INT, comment VARCHAR(50))
INSERT INTO @Comments VALUES (1, 'Bob is a sales person'), (2, 'Shane is a nice guy')
DECLARE @People TABLE (personname VARCHAR(50))
INSERT INTO @People VALUES ('Bob'), ('Mark'), ('Shane')

示例数据：

+----+-----------------------+
| id |        comment        |
+----+-----------------------+
|  1 | Bob is a sales person |
|  2 | Shane is a nice guy   |
+----+-----------------------+

预定义词：

+------------+
| personname |
+------------+
| Bob        |
| Mark       |
| Shane      |
+------------+

注意：

• 请查看附加的图片以获取示例数据。
• 如果评论列中包含预定义单词中的人名，则我想用 *** 替换/掩码。

- Tushar More

---

提供样例数据和期望输出以及您的尝试将更有帮助！ - Prashant Pimpale

我已经附上了样本数据的图片。 - Tushar More

图片不易处理，因此我要求提供样本数据，这样用户就可以复制相关数据并进行操作。 - Prashant Pimpale

哪个数据库版本？SQL Server 2017引入了数据脱敏。 - Panagiotis Kanavos

我有SQL Server 2016 @PanagiotisKanavos - Tushar More

Answer 1

如果你有一个Bob和一个Bobby，你不能先替换Bob，否则你会得到***by。这里有一种按长度倒序替换字符串的方法，这是一个开始。

不过，如果Bob是一个雪车手，那么你最终会得到***是一个***sledder。

DECLARE @Comments TABLE (id INT, comment VARCHAR(50))
INSERT INTO @Comments VALUES (1, 'Bob is a sales person'), (2, 'Shane is a nice guy')
DECLARE @People TABLE (personname VARCHAR(50))
INSERT INTO @People VALUES ('Bob'), ('Mark'), ('Shane')

DECLARE @curname VARCHAR(50)
DECLARE cur CURSOR FOR   
SELECT personname FROM @people ORDER BY LEN(personname) DESC, personname desc

OPEN cur  

FETCH NEXT FROM cur   
INTO @curname

WHILE @@FETCH_STATUS = 0  
BEGIN       
    UPDATE @Comments SET comment = REPLACE(comment, @curname, REPLICATE('*', LEN(@curname)))

    FETCH NEXT FROM cur   
    INTO @curname
END   
CLOSE cur;  
DEALLOCATE cur;  

输出：

SELECT * FROM @Comments

id  comment
1   *** is a sales person
2   ***** is a nice guy

一些快速修改：

1）替换大小写敏感：

UPDATE @Comments SET comment = REPLACE(comment COLLATE Latin1_General_CS_AS, @curname, REPLICATE('*', LEN(@curname)))

2) 仅替换第一个实例：

UPDATE @Comments SET comment = CASE WHEN CHARINDEX(@curname, comment) > 0   
    THEN STUFF(comment, CHARINDEX(@curname, comment), LEN(@curname), REPLICATE('*', LEN(@curname)))
    ELSE comment
    END  

- Max Szczurek

---

正确而有用的答案。谢谢，它也帮助了我。 - Naim Halai

Answer 2

从SQL Server 2017开始，您可以使用STRING_AGG（更多信息在此处）与STRING_SPLIT一起将句子分解为单词，用***替换名称，然后将单词聚合回句子：

select co.id, 
      string_agg(case when p.personname is null then c.value else '***' end,' ') as comment
from @Comments co
cross apply string_split(comment,' ') c
left join @People p on p.personname = c.value
group by co.id

结果：

如果单词之间有除空格以外的字符，您可以在分割之前将它们替换为空格：

DECLARE @Comments TABLE (id INT, comment VARCHAR(50))
INSERT INTO @Comments VALUES
  (1, 'Bob (is a sales person)')
, (2, '(Shane:is-a-nice-guy)')
, (3, 'This.is(Mark),he.is-a-nice-guy,too')
DECLARE @People TABLE (personname VARCHAR(50))
INSERT INTO @People VALUES ('Bob'), ('Mark'), ('Shane')

select co.id, 
      trim(string_agg(case when p.personname is null then c.value else '***' end,' ')) as comment
from
(
select id, replace(replace(replace(replace(replace(replace(comment,':',' '),')',' '),'(',' '),',',' '),'.',' '),'-',' ') as comment 
from @Comments
) co
cross apply string_split(comment,' ') c
left join @People p on p.personname = c.value
group by co.id

现在有这种输入：

你会得到这个输出：

- Andrea

---

推测仅通过空格切割可能不够精确。可能会有括号、点号、逗号等符号。在大多数情况下，进行整词搜索并不是一件简单的事情... - Shnugo

@Shnugo 你说得对，我首先想到的是在分割之前用空格替换所有“不需要”的字符。 - Andrea

@Andrea 我的应用程序生产环境没有SQL Server 2017。在这种情况下，有什么解决方案？ - Tushar More