SQL Server中拆分超20字符ADDRESS_1至ADDRESS_2的实现需求

核心需求 #

当ADDRESS_1字段长度超过20字符时，以20字符位置前最近的空格为拆分点，将前半部分保留在ADDRESS_1，后半部分移入空白的ADDRESS_2；长度不超过20的字段保持原样，ADDRESS_2留空。

实现SQL语句 #

验证语句（先确认结果再执行更新）

SELECT 
    ADDRESS_1 AS Original_Address,
    CASE 
        WHEN LEN(ADDRESS_1) > 20 THEN 
            SUBSTRING(ADDRESS_1, 1, 20 - CHARINDEX(' ', REVERSE(SUBSTRING(ADDRESS_1, 1, 20))))
        ELSE ADDRESS_1
    END AS New_ADDRESS_1,
    CASE 
        WHEN LEN(ADDRESS_1) > 20 THEN 
            LTRIM(SUBSTRING(ADDRESS_1, 20 - CHARINDEX(' ', REVERSE(SUBSTRING(ADDRESS_1, 1, 20))) + 1, LEN(ADDRESS_1)))
        ELSE ''
    END AS New_ADDRESS_2
FROM YourTableName
WHERE ADDRESS_1 IS NOT NULL;

正式更新语句

UPDATE YourTableName
SET 
    ADDRESS_1 = CASE 
                   WHEN LEN(ADDRESS_1) > 20 THEN 
                       SUBSTRING(ADDRESS_1, 1, 20 - CHARINDEX(' ', REVERSE(SUBSTRING(ADDRESS_1, 1, 20))))
                   ELSE ADDRESS_1
               END,
    ADDRESS_2 = CASE 
                   WHEN LEN(ADDRESS_1) > 20 THEN 
                       LTRIM(SUBSTRING(ADDRESS_1, 20 - CHARINDEX(' ', REVERSE(SUBSTRING(ADDRESS_1, 1, 20))) + 1, LEN(ADDRESS_1)))
                   ELSE '' -- 需保留NULL则改为NULL
               END
WHERE ADDRESS_1 IS NOT NULL;

关键逻辑解释 #

1. 长度判断：LEN(ADDRESS_1) > 20筛选出需要拆分的超长地址
2. 定位拆分点：
   • SUBSTRING(ADDRESS_1, 1, 20)截取前20个字符，限定空格查找范围
   • REVERSE(...)反转前20字符，让CHARINDEX可以从后往前定位第一个空格（即原字符串前20字符内的最后一个空格）
   • 20 - CHARINDEX(' ', REVERSE(...))计算出原字符串中拆分点的位置
3. 拆分处理：
   • 前半部分截取到拆分点，保留在ADDRESS_1
   • 后半部分从拆分点后一位开始截取，用LTRIM去除开头多余空格后存入ADDRESS_2

示例验证 #

原ADDRESS_1：14256 Canyonville Drive apt 23（长度28）

• 前20字符：14256 Canyonville Dr
• 反转后：rD evillenyanoC 65241
• CHARINDEX找到空格位置为3，拆分点计算为20-3=17
• 拆分后：
  • ADDRESS_1：14256 Canyonville
  • ADDRESS_2：Drive apt 23

注意事项 #

• 替换YourTableName为实际表名
• 若前20字符内无空格（极端情况），CHARINDEX返回0，此时ADDRESS_1会取前20字符，ADDRESS_2取剩余部分；如需特殊处理这种情况，可在CASE中添加额外判断：

CASE 
    WHEN LEN(ADDRESS_1) > 20 THEN 
        CASE WHEN CHARINDEX(' ', REVERSE(SUBSTRING(ADDRESS_1, 1, 20))) > 0 THEN
            SUBSTRING(ADDRESS_1, 1, 20 - CHARINDEX(' ', REVERSE(SUBSTRING(ADDRESS_1, 1, 20))))
        ELSE
            SUBSTRING(ADDRESS_1, 1, 20) -- 无空格时直接截断前20字符
        END
    ELSE ADDRESS_1
END

• 执行UPDATE前务必用SELECT语句验证结果，避免误操作

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