如何在SQL中查找并删除包含Unicode损坏字符的记录

问题背景 #

这类报错本质是数据库中存储了非法的未配对Unicode代理项字符，这类字符无法被转换为指定代码页的编码，所以触发EncoderFallbackException。你之前的查询失效是因为�是Unicode解码失败后的展示字符，并非数据库中实际存储的原始值，直接匹配�无法定位到真实异常记录。

报错原文 #

ERROR：数据集"DataSet1"中发生异常。详细信息：System.Text.EncoderFallbackException：无法将索引184处的Unicode字符�转换为指定代码页。

定位异常记录的方法 #

• 方法1：利用TRY_CONVERT快速筛选（适合海量表，性能最优）
  利用TRY_CONVERT的容错特性，尝试把字符串转换为你目标代码页对应的编码类型，返回NULL的即为包含不可转换异常字符的行：

-- 代码页936对应GBK编码，如果你使用其他编码可替换为对应代码页：比如西欧编码1252、UTF-8 65001
SELECT *
FROM dbo.TestTable
WHERE TRY_CONVERT(VARCHAR(MAX), SomeString, 936) IS NULL

• 方法2：精准匹配非法Unicode代理项范围
  所有导致这类报错的无效字符都集中在Unicode代理对范围U+D800~U+DFFF，使用二进制排序规则匹配该范围即可定位：

SELECT *
FROM dbo.TestTable
WHERE SomeString LIKE N'%[' + NCHAR(0xD800) + '-' + NCHAR(0xDFFF) + ']%' 
COLLATE Latin1_General_BIN2

清理方案 #

• 直接删除异常行（操作前务必做好数据备份）

DELETE FROM dbo.TestTable
WHERE SomeString LIKE N'%[' + NCHAR(0xD800) + '-' + NCHAR(0xDFFF) + ']%' 
COLLATE Latin1_General_BIN2

• 保留行仅清理异常字符
  可以自定义标量函数遍历字符串每个字符，过滤掉编码在0xD800~0xDFFF区间的字符后替换原字段值即可。

内容的提问来源于stack exchange，提问作者Nuri Ensing