SQL Server如何拆分单列地址字符串并赋值到其他列且不产生额外行

实现逻辑 #

完全匹配指定拆分规则：

• 第一步：提取地址中最后一个逗号之后的所有内容，去除首尾空白字符
• 第二步：从上述内容中提取开头的连续数字作为邮编（post_office）
• 第三步：邮编后面的所有内容（无需额外拆分空格）去除首尾空白后作为城市（city）

完整代码示例 #

写法1：CTE分层写法（可读性高，方便调试） #

WITH split_step1 AS (
    -- 第一步：提取最后一个逗号之后的内容，去除首尾空格
    SELECT 
        address,
        TRIM(RIGHT(address, LEN(address) - CHARINDEX(',', REVERSE(address)))) AS after_last_comma
    FROM 你的表名
),
split_step2 AS (
    -- 第二步：定位邮编（连续数字）的结束位置
    SELECT
        address,
        after_last_comma,
        PATINDEX('%[^0-9]%', after_last_comma) AS post_code_end_pos
    FROM split_step1
)
-- 最终提取结果
SELECT
    address,
    -- 提取邮编：从开头到非数字字符之前的内容
    CASE WHEN post_code_end_pos > 1 THEN LEFT(after_last_comma, post_code_end_pos - 1) ELSE NULL END AS post_office,
    -- 提取城市：邮编结束位置之后的所有内容，去除首尾空格
    CASE WHEN post_code_end_pos > 1 THEN TRIM(SUBSTRING(after_last_comma, post_code_end_pos, LEN(after_last_comma))) ELSE NULL END AS city
FROM split_step2

写法2：嵌套函数写法（适合直接写进UPDATE语句） #

如果需要直接更新表中的post_office和city字段，可以直接用以下嵌套写法：

UPDATE 你的表名
SET 
    post_office = CASE WHEN PATINDEX('%[^0-9]%', TRIM(RIGHT(address, LEN(address) - CHARINDEX(',', REVERSE(address))))) > 1
                   THEN LEFT(TRIM(RIGHT(address, LEN(address) - CHARINDEX(',', REVERSE(address)))), PATINDEX('%[^0-9]%', TRIM(RIGHT(address, LEN(address) - CHARINDEX(',', REVERSE(address))))) - 1)
                   ELSE NULL END,
    city = CASE WHEN PATINDEX('%[^0-9]%', TRIM(RIGHT(address, LEN(address) - CHARINDEX(',', REVERSE(address))))) > 1
                THEN TRIM(SUBSTRING(TRIM(RIGHT(address, LEN(address) - CHARINDEX(',', REVERSE(address)))), PATINDEX('%[^0-9]%', TRIM(RIGHT(address, LEN(address) - CHARINDEX(',', REVERSE(address))))), LEN(TRIM(RIGHT(address, LEN(address) - CHARINDEX(',', REVERSE(address)))))))
                ELSE NULL END

适配场景验证 #

城市带多个空格的场景可以正常拆分：
比如地址为'Marco Polo street 8a, 44300 Vienna Old Town'，拆分结果为：

• post_office: 44300
• city: Vienna Old Town

异常兼容说明 #

代码中加了CASE判断，遇到以下异常场景会返回NULL，不会报错：

• 地址中没有逗号
• 最后一个逗号之后的内容不是数字开头
• 最后一个逗号之后全是数字没有城市内容

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