SQL中如何用任意分隔符拆分5位邮政编码字符串？

针对任意符号/字符作为分隔符的邮政编码字符串，提供两种实用SQL解决方案：

方法1：统一替换非数字字符为逗号后拆分 #

核心思路是将所有非数字字符替换为逗号，清理多余逗号后，用STRING_SPLIT拆分处理：

DECLARE @Zipcodes nvarchar(max) = '78257, 10101, 91111,99999,  11111 222222 | 33333'

-- 1. 将所有非数字字符替换为逗号
WHILE PATINDEX('%[^0-9]%', @Zipcodes) > 0
BEGIN
    SET @Zipcodes = STUFF(@Zipcodes, PATINDEX('%[^0-9]%', @Zipcodes), 1, ',')
END

-- 2. 清除连续重复的逗号
WHILE PATINDEX('%,,%', @Zipcodes) > 0
BEGIN
    SET @Zipcodes = REPLACE(@Zipcodes, ',,', ',')
END

-- 3. 去除首尾的逗号
SET @Zipcodes = LTRIM(RTRIM(@Zipcodes))
SET @Zipcodes = CASE WHEN LEFT(@Zipcodes,1) = ',' THEN STUFF(@Zipcodes,1,1,'') ELSE @Zipcodes END
SET @Zipcodes = CASE WHEN RIGHT(@Zipcodes,1) = ',' THEN STUFF(@Zipcodes,LEN(@Zipcodes),1,'') ELSE @Zipcodes END

-- 拆分并匹配表中记录
SELECT * 
FROM BranchWithZipcode 
WHERE zipcode IN (
    SELECT CAST(Value AS int) 
    FROM STRING_SPLIT(@Zipcodes, ',')
    WHERE LEN(Value) = 5 -- 过滤非5位的无效邮编
)

方法2：递归CTE精准提取5位邮政编码 #

利用递归CTE逐个提取字符串中的5位数字，无需修改原字符串，适合严格匹配邮编格式的场景：

DECLARE @Zipcodes nvarchar(max) = '78257, 10101, 91111,99999,  11111 222222 | 33333'

;WITH ZipCTE AS (
    -- 初始步骤：提取第一个5位邮编，剩余字符串截去已提取部分
    SELECT 
        SUBSTRING(@Zipcodes, PATINDEX('%[0-9][0-9][0-9][0-9][0-9]%', @Zipcodes), 5) AS Zipcode,
        STUFF(@Zipcodes, 1, PATINDEX('%[0-9][0-9][0-9][0-9][0-9]%', @Zipcodes) + 4, '') AS RemainingString
    WHERE PATINDEX('%[0-9][0-9][0-9][0-9][0-9]%', @Zipcodes) > 0

    UNION ALL

    -- 递归步骤：从剩余字符串中继续提取5位邮编
    SELECT 
        SUBSTRING(RemainingString, PATINDEX('%[0-9][0-9][0-9][0-9][0-9]%', RemainingString), 5) AS Zipcode,
        STUFF(RemainingString, 1, PATINDEX('%[0-9][0-9][0-9][0-9][0-9]%', RemainingString) + 4, '') AS RemainingString
    FROM ZipCTE
    WHERE PATINDEX('%[0-9][0-9][0-9][0-9][0-9]%', RemainingString) > 0
)

-- 匹配表中记录，去重避免重复结果
SELECT DISTINCT b.*
FROM BranchWithZipcode b
JOIN (
    SELECT CAST(Zipcode AS int) AS Zipcode 
    FROM ZipCTE
) z ON b.zipcode = z.Zipcode

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