含隐藏ASCII字符的SQL字符串比较失效，求优化方案

针对你遇到的隐藏ASCII字符（如制表符、换行、零宽度空格等）导致字符串比较不准确的问题，以下是几种实用的优化方法：

1. 清理非打印ASCII字符后再比较 #

先过滤字符串中的非打印字符，只保留可见ASCII字符（ASCII码32-126，包含空格、字母、数字和常用符号），再进行拼接比较。

方法1：创建自定义清理函数 #

如果需要多次使用，建议创建用户定义函数统一处理：

CREATE FUNCTION dbo.CleanNonPrintableChars(@input NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @i INT = 1
    WHILE @i <= LEN(@input)
    BEGIN
        -- 仅保留ASCII 32到126的可见字符，其余替换为空
        IF ASCII(SUBSTRING(@input, @i, 1)) NOT BETWEEN 32 AND 126
            SET @input = STUFF(@input, @i, 1, '')
        ELSE
            SET @i = @i + 1
    END
    RETURN @input
END

之后在查询中调用函数完成清理和比较：

SELECT TestChemicalName, ResultChemicalName,
CASE 
    WHEN 
        dbo.CleanNonPrintableChars(LAB_TestChemicalName + LAB_ResultChemicalName) 
        = 
        dbo.CleanNonPrintableChars(TestChemicalName + ResultChemicalName) 
    THEN NULL 
    ELSE LAB_TestChemicalName + ' ' + LAB_ResultChemicalName
END AS FinalElementName
FROM dbo.chemicalTraceTesting

方法2：直接嵌套清理逻辑（临时场景） #

如果不需要复用，可直接在查询中用嵌套REPLACE处理常见非打印字符：

SELECT TestChemicalName, ResultChemicalName,
CASE 
    WHEN 
        REPLACE(REPLACE(LAB_TestChemicalName + LAB_ResultChemicalName, CHAR(9), ''), CHAR(10), '')
        =
        REPLACE(REPLACE(TestChemicalName + ResultChemicalName, CHAR(9), ''), CHAR(10), '')
    THEN NULL 
    ELSE LAB_TestChemicalName + ' ' + LAB_ResultChemicalName
END AS FinalElementName
FROM dbo.chemicalTraceTesting

注：可根据实际情况添加更多REPLACE处理其他非打印字符，如CHAR(13)（回车）、CHAR(0)（空字符）等。

2. 使用二进制值直接比较 #

字符串的二进制表示会完全暴露所有字符（包括隐藏字符），直接对比二进制值可精准判断字符串是否完全一致：

SELECT TestChemicalName, ResultChemicalName,
CASE 
    WHEN 
        CAST(LAB_TestChemicalName + LAB_ResultChemicalName AS VARBINARY(MAX))
        =
        CAST(TestChemicalName + ResultChemicalName AS VARBINARY(MAX))
    THEN NULL 
    ELSE LAB_TestChemicalName + ' ' + LAB_ResultChemicalName
END AS FinalElementName
FROM dbo.chemicalTraceTesting

这种方法无需修改原字符串，适合需要保留原始内容但要精准比较的场景。

3. 排查隐藏字符的辅助手段 #

若要定位具体是哪些隐藏字符导致差异，可通过以下查询查看字符串的ASCII码分布：

SELECT 
    SUBSTRING(LAB_TestChemicalName + LAB_ResultChemicalName, number, 1) AS 字符,
    ASCII(SUBSTRING(LAB_TestChemicalName + LAB_ResultChemicalName, number, 1)) AS ASCII码
FROM master.dbo.spt_values
WHERE type = 'P' AND number BETWEEN 1 AND LEN(LAB_TestChemicalName + LAB_ResultChemicalName)
-- 替换为有问题的记录ID
AND chemicalTraceTestingID = '目标记录ID'

对比两边字符串的ASCII码，就能快速找到隐藏的非打印字符。

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