SQL Server中CONCAT函数遇特殊非空值的异常行为分析及解决方法问询

老哥，你遇到的这个异常其实很好解释——那个看起来空的NameStudent根本不是空值，而是两个不可见的控制字符，这才导致了LEN返回2、CONCAT结果显示异常的情况，具体分两种最常见的场景：

1. 包含ASCII空字符（CHAR(0)） #

SQL Server里，ASCII码0对应的CHAR(0)是字符串终止符，就像给字符串加了个“到此为止”的标记：

• LEN(NameStudent)会老老实实返回实际长度2，因为它计算的是字符总数，包括这个看不见的终止符；
• 但SSMS显示结果时，一碰到CHAR(0)就会停止显示后面的内容，所以CONCAT('a', CHAR(0)+CHAR(0), 'b')的真实值是a + 两个终止符 + b，但SSMS只显示到第一个终止符前的a，让你误以为b没被拼接上；
• 至于NameStudent看起来是空，完全是因为CHAR(0)本身不可见，还终止了显示。

2. 包含退格/换行这类控制字符 #

如果NameStudent是两个退格符（CHAR(8)）：

• LEN返回2没问题，退格符也算字符；
• 拼接后显示时，退格符会“删掉”前面的字符：a加两个退格会先把a删掉，再尝试删更前面的内容（没有内容），最后加的b可能被退格操作影响，显示异常；
  要是回车+换行（CHAR(13)+CHAR(10)）：
• 拼接后的字符串会在单元格内换行，a在第一行，b在第三行，你可能只看到第一行的a，以为b没了。

先验证一下真实值！ #

你可以跑下面的查询，直接看穿这个“空值”的真面目：

SELECT 
    ShouldEndWithb,
    NameStudent,
    LEN(NameStudent) as Length,
    -- 查看每个字符的ASCII码，直接揪出控制字符
    ASCII(SUBSTRING(NameStudent,1,1)) AS FirstCharASCII,
    ASCII(SUBSTRING(NameStudent,2,1)) AS SecondCharASCII,
    -- 把控制字符换成可见标记，一眼看懂
    REPLACE(REPLACE(NameStudent, CHAR(0), '[空终止符]'), CHAR(8), '[退格符]') AS 可见化NameStudent,
    -- 看看拼接后的真实长度，证明b其实存在
    LEN(ShouldEndWithb) AS 拼接后真实长度
FROM #Student

如果FirstCharASCII返回0，那就是CHAR(0)；返回8是退格符；返回13和10就是回车换行。而且拼接后真实长度肯定是4（a+2字符+b），说明b一直都在，只是没显示出来。

解决办法：清洗控制字符 #

根据你查到的控制字符类型，选对应的方法就行：

方法1：一锅端所有不可见控制字符 #

直接移除所有ASCII码0-31、127的非打印字符，适合不确定具体是什么控制字符的情况：

SELECT 
    CONCAT('a', ISNULL(
        -- 把所有非打印控制字符替换为空
        REPLACE(NameStudent, SUBSTRING(NameStudent, PATINDEX('%[' + CHAR(0)+'-'+CHAR(31)+CHAR(127)+']%', NameStudent), 1), ''), 
        'ccc'), 'b') as ShouldEndWithb,
    NameStudent,
    LEN(NameStudent) as Length
FROM #Student

方法2：精准处理特定字符 #

如果确认是CHAR(0)，直接针对性替换：

SELECT 
    CONCAT('a', ISNULL(REPLACE(NameStudent, CHAR(0), ''), 'ccc'), 'b') as ShouldEndWithb,
    NameStudent,
    LEN(NameStudent) as Length
FROM #Student

方法3：处理空白类控制字符 #

如果是换行、空格这类空白控制字符，用修剪+替换组合拳：

SELECT 
    CONCAT('a', ISNULL(
        LTRIM(RTRIM(REPLACE(REPLACE(NameStudent, CHAR(10), ''), CHAR(13), ''))), 
        'ccc'), 'b') as ShouldEndWithb,
    NameStudent,
    LEN(NameStudent) as Length
FROM #Student

额外提醒 #

要是这些控制字符是数据导入时带进来的，建议在入库前就做清洗，省得后续查询再踩坑~

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