如何在SQL Server中合并不同长度的重复值？

原始查询语句 #

SELECT 
    *, 
    LEN(Test) LENofTEST, 
    DATALENGTH(Test) DataLENofTEST, 
    CAST(Test AS VARBINARY(30)) ExtraColumn
FROM
    (SELECT 
         TRIM(SOS1 + ',' + REPLACE(PANO20, ' ', '')) AS 'Test', 
         COUNT(SOS1 + ',' + REPLACE(PANO20, ' ', '')) AS 'COUNTR'
     FROM 
         MyServerName.dbo.MyTableName
     WHERE 
         PANO20 LIKE '%701001207%'
     GROUP BY 
         TRIM(SOS1 + ',' + REPLACE(PANO20, ' ', ''))) A

查询结果 #

Test            COUNTR   LENofTEST   DataLENofTEST   ExtraColumn
-----------------------------------------------------------------
100,701001207   1        13             13   0x3130302C373031303031323037
100,701001207   1        14             14   0x3130302C373031303031323037A0
123,701001207   1        13             13   0x3132332C373031303031323037

问题分析 #

前两行Test值视觉上完全一致，但SQL将它们判定为不同值，导致COUNTR均为1而非预期的2。从ExtraColumn的二进制值可看出，第二行末尾多了0xA0——这是非断空格（NBSP），SQL默认的TRIM()仅去除普通空格（ASCII 32，即0x20），无法识别并清除非断空格，因此两个字符串长度不同，GROUP BY时被分成两组。

解决方案 #

1. 针对性替换非断空格 #

在字符串处理逻辑中增加对非断空格（CHAR(160)）的替换，确保所有空格类字符都被清理：

SELECT 
    CleanedTest AS Test,
    COUNT(*) AS COUNTR,
    LEN(CleanedTest) AS LENofTEST,
    DATALENGTH(CleanedTest) AS DataLENofTEST,
    CAST(CleanedTest AS VARBINARY(30)) AS ExtraColumn
FROM
    (SELECT 
         -- 先替换普通空格和非断空格，再执行TRIM
         TRIM(REPLACE(REPLACE(SOS1 + ',' + PANO20, ' ', ''), CHAR(160), '')) AS CleanedTest
     FROM 
         MyServerName.dbo.MyTableName
     WHERE 
         PANO20 LIKE '%701001207%') A
GROUP BY 
    CleanedTest

2. 扩展清理范围（处理更多不可见字符） #

如果数据中还存在制表符、换行符等其他不可见字符，可进一步扩展替换逻辑：

SELECT 
    CleanedTest AS Test,
    COUNT(*) AS COUNTR,
    LEN(CleanedTest) AS LENofTEST,
    DATALENGTH(CleanedTest) AS DataLENofTEST,
    CAST(CleanedTest AS VARBINARY(30)) AS ExtraColumn
FROM
    (SELECT 
         TRIM(
             REPLACE(
                 REPLACE(
                     REPLACE(SOS1 + ',' + PANO20, ' ', ''),
                     CHAR(160), ''), -- 非断空格
                 CHAR(9), '') -- 制表符
         ) AS CleanedTest
     FROM 
         MyServerName.dbo.MyTableName
     WHERE 
         PANO20 LIKE '%701001207%') A
GROUP BY 
    CleanedTest

3. 通用清理所有非打印字符（复杂场景） #

如果数据存在多种未知非打印字符，可使用递归CTE配合PATINDEX清除所有非可打印字符：

WITH CleanedStrings AS (
    SELECT 
        SOS1 + ',' + PANO20 AS RawString
    FROM MyServerName.dbo.MyTableName
    WHERE PANO20 LIKE '%701001207%'
    UNION ALL
    SELECT 
        REPLACE(RawString, SUBSTRING(RawString, PATINDEX('%[^ -~]%', RawString), 1), '')
    FROM CleanedStrings
    WHERE PATINDEX('%[^ -~]%', RawString) > 0
)
SELECT 
    TRIM(REPLACE(RawString, ' ', '')) AS Test,
    COUNT(*) AS COUNTR,
    LEN(TRIM(REPLACE(RawString, ' ', ''))) AS LENofTEST,
    DATALENGTH(TRIM(REPLACE(RawString, ' ', ''))) AS DataLENofTEST,
    CAST(TRIM(REPLACE(RawString, ' ', '')) AS VARBINARY(30)) AS ExtraColumn
FROM CleanedStrings
WHERE PATINDEX('%[^ -~]%', RawString) = 0
GROUP BY TRIM(REPLACE(RawString, ' ', ''))
OPTION (MAXRECURSION 100)

说明 #

所有方案核心是统一字符串清理逻辑，确保GROUP BY和SELECT使用完全相同的处理规则，同时覆盖所有可能导致字符串“视觉相同、实际不同”的不可见字符。

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