将特殊字符替换为 HTML 实体

Question

我在表TABLE中有以下内容

id    content
-------------------------------------
1     Hellö world, I äm text
2     ènd there äré many more chars
3     that are speçial in my dat£base

我现在需要使用 bcp 将这些记录导出到 HTML 文件中：

set @command = 'bcp "select [content] from [TABLE] where [id] = ' + 
@id queryout +' + @filename + '.html" -S ' + @instance +
' -c -U ' + @username + ' -P ' + @password"
exec xp_cmdshell @command, no_ouput

为了使输出看起来正确，我需要首先将所有特殊字符替换为它们各自的 HTML 实体(伪(

insert into [#temp_html] ..
replace(replace([content], 'ö', 'ö'), 'ä', 'ä')

但是到目前为止，我已经有 30 个嵌套replace，它开始看起来很疯狂。

经过多次搜索，我找到了这篇文章，它使用 HTML 转换表，但它太高级了，我无法理解：

1. 该表没有列出特殊字符本身，因为它们在我的文本(ö, à等(中，而是列出 UnicodeHex。我是否需要将它们添加到表中才能进行所需的转换？
2. 我无法理解如何更新脚本以替换所有特殊字符。有人可以给我看一段(伪(代码吗？

Answer 1

使用转换表执行此操作的一种方法是使用递归 cte 进行替换，再使用 cte 仅获取每个转换值的最后一行。

首先，创建并填充示例表(请在以后的问题中保存此步骤(：

DECLARE @T AS TABLE
(
id int,
content nvarchar(100)
)
INSERT INTO @T (id, content) VALUES
(1,     'Hellö world, I äm text'),
(2,     'ènd there äré many more chars'),
(3,     'that are speçial in my dat£base')

然后，创建并填充转换表(我不知道这些字符的 HTML 实体，所以我只使用了数字 [而且在结果中更容易看到](。另外，请注意，这可以使用链中的另一个 cte 来完成。

DECLARE @Translations AS TABLE
(
str nchar(1),
replacement nvarchar(10)
)
INSERT INTO @Translations (str, replacement) VALUES
('ö', '-1-'),
('ä', '-2-'),
('è', '-3-'),
('ä', '-4-'),
('é', '-5-'),
('ç', '-6-'),
('£', '-7-')

现在，第一个 cte 将进行替换，第二个 cte 只是添加一个row_number，以便对于每个 id，lvl 的最后一个值将得到 1：

;WITH CTETranslations AS
(
SELECT id, content, 1 As lvl
FROM @T
UNION ALL
SELECT id, CAST(REPLACE(content, str, replacement) as nvarchar(100)), lvl+1
FROM CTETranslations
JOIN @Translations 
ON content LIKE '%' + str + '%' 
), cteNumberedTranslation AS
(
SELECT id, content, ROW_NUMBER() OVER(PARTITION BY Id ORDER BY lvl DESC) rn
FROM CTETranslations
)

从第二个 cte 中选择 rn = 1，我已经连接了原始表以并排显示源和翻译：

SELECT r.id, s.content, r.content
FROM @T s
JOIN cteNumberedTranslation r
ON s.Id = r.Id
WHERE rn = 1
ORDER BY Id

结果：

id  content                             content
1   Hellö world, I äm text              Hell-1- world, I -4-m text
2   ènd there äré many more chars       -3-nd there -4-r-5- many more chars
3   that are speçial in my dat£base     that are spe-6-ial in my dat-7-base

请注意，如果您的内容包含超过 100 个特殊字符，则需要在最终选择中添加maxrecursion 0提示：

SELECT r.id, s.content, r.content
FROM @T s
JOIN cteNumberedTranslation r
ON s.Id = r.Id
WHERE rn = 1
ORDER BY Id
OPTION ( MAXRECURSION 0 );

在 rextester 上观看现场演示。