SQL Server中STRING_SPLIT函数无法拆分数据库列全部字符串问题

我需要从LWArchive表的Notes（varchar(max)）字段中提取所有唯一的条码值。表结构创建及数据插入语句如下：

CREATE TABLE LWArchive (
Number int,
Notes varchar(MAX))

INSERT INTO LWArchive(Number,Notes)
VALUES(1,'OGC 503360 / 503361 M303834 M303838 M303835 M303836 M303837 M303839 M303840 M303841 303842'),
(2,'OGC = Q.6773'),
(3,'DEED REF = 0001'),
(4,'OGC 50336 / 50336 M30383 M03038 M30383 M30383 M30383 M00303  M303840 M303841 M303842')

Notes字段包含多个以空格分隔的条码（例如'M000001 M000002 M000003'）。我尝试用以下查询拆分Notes列：

select
a.ID,
s.value
from Archive a
CROSS APPLY STRING_SPLIT(a.notes, N' ') s

但拆分结果不一致，部分条码未被正确拆分；但手动将字符串输入STRING_SPLIT函数时，拆分功能正常。我使用的是SQL Server 2019，已将数据库兼容级别从110调整为130以支持STRING_SPLIT函数。

问题原因与解决方案 #

核心原因 #

拆分异常的本质是Notes字段中存在连续空格（比如第4条记录里的M00303 M303840包含两个连续空格），或是存在非标准空格字符（如制表符、全角空格等）。STRING_SPLIT按单个空格拆分时，会产生空值行，同时连续空格会打乱拆分逻辑。

方案1：先清理连续空格再拆分 #

先将Notes中的连续空格替换为单个空格，再执行拆分：

SELECT 
    a.Number,
    s.value
FROM LWArchive a
CROSS APPLY STRING_SPLIT(
    -- 替换连续空格为单个空格
    REPLACE(a.Notes, '  ', ' '), 
    ' '
) s
-- 过滤空值和非条码内容（可根据实际条码规则调整匹配条件）
WHERE s.value <> '' AND s.value LIKE '[M0-9]%';

方案2：清理所有非标准空格（SQL Server 2017+适用） #

如果存在制表符、全角空格等非标准空格，可先用TRANSLATE统一转换为普通空格，再压缩连续空格：

WITH CleanedNotes AS (
    SELECT 
        Number,
        -- 替换各类非标准空格为普通空格，再去除首尾空格、压缩连续空格
        LTRIM(RTRIM(
            REPLACE(
                TRANSLATE(Notes, CHAR(9)+CHAR(10)+CHAR(13)+N'　', '    '), 
                '  ', ' '
            )
        )) AS CleanedNote
    FROM LWArchive
)
SELECT 
    c.Number,
    s.value
FROM CleanedNotes c
CROSS APPLY STRING_SPLIT(c.CleanedNote, ' ') s
WHERE s.value <> '';

提取唯一条码值 #

如果需要获取所有唯一的条码，可在上述查询基础上添加DISTINCT：

SELECT DISTINCT s.value AS UniqueBarcode
FROM LWArchive a
CROSS APPLY STRING_SPLIT(
    REPLACE(a.Notes, '  ', ' '), 
    ' '
) s
WHERE s.value <> '' AND s.value LIKE '[M0-9]%';

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