按条件拆分地址字符串且不截断单词，实现多列分配

要实现每个地址字段长度<30且不截断单词的需求，可以通过反向查找空格位置的方式精准拆分，避免直接用SUBSTRING截断单词的问题。以下是基于SQL Server的分步实现方案：

核心思路 #

对每个字段，若长度≥30，就找到前30个字符内最后一个空格的位置，以此为拆分点：

• 空格前的部分保留在原字段
• 空格后的部分追加到下一个字段（与原下字段内容用空格连接）
• 依次处理address1→address2、address2→address3、address3→address4

完整SQL代码 #

WITH Step1 AS (
    -- 处理address1到address2
    SELECT
        -- 若address1长度≥30，取前30字符内最后一个空格前的内容；否则保留原内容
        CASE WHEN LEN(address1) >= 30 
             THEN SUBSTRING(address1, 1, 30 - CHARINDEX(' ', REVERSE(SUBSTRING(address1, 1, 30))))
             ELSE address1
        END AS address1,
        -- 把address1拆分出的剩余部分，和原address2拼接（加空格分隔）
        CASE WHEN LEN(address1) >= 30
             THEN LTRIM(SUBSTRING(address1, 30 - CHARINDEX(' ', REVERSE(SUBSTRING(address1, 1, 30))) + 1, 8000)) 
                  + CASE WHEN address2 IS NOT NULL AND address2 <> '' THEN ' ' + address2 ELSE '' END
             ELSE address2
        END AS address2,
        address3,
        address4
    FROM YourTableName -- 替换为你的表名
),
Step2 AS (
    -- 处理address2到address3
    SELECT
        address1,
        CASE WHEN LEN(address2) >= 30
             THEN SUBSTRING(address2, 1, 30 - CHARINDEX(' ', REVERSE(SUBSTRING(address2, 1, 30))))
             ELSE address2
        END AS address2,
        CASE WHEN LEN(address2) >= 30
             THEN LTRIM(SUBSTRING(address2, 30 - CHARINDEX(' ', REVERSE(SUBSTRING(address2, 1, 30))) + 1, 8000))
                  + CASE WHEN address3 IS NOT NULL AND address3 <> '' THEN ' ' + address3 ELSE '' END
             ELSE address3
        END AS address3,
        address4
    FROM Step1
),
Step3 AS (
    -- 处理address3到address4
    SELECT
        address1,
        address2,
        CASE WHEN LEN(address3) >= 30
             THEN SUBSTRING(address3, 1, 30 - CHARINDEX(' ', REVERSE(SUBSTRING(address3, 1, 30))))
             ELSE address3
        END AS address3,
        CASE WHEN LEN(address3) >= 30
             THEN LTRIM(SUBSTRING(address3, 30 - CHARINDEX(' ', REVERSE(SUBSTRING(address3, 1, 30))) + 1, 8000))
                  + CASE WHEN address4 IS NOT NULL AND address4 <> '' THEN ' ' + address4 ELSE '' END
             ELSE address4
        END AS address4
    FROM Step2
)
-- 输出最终处理结果
SELECT * FROM Step3;

代码说明 #

• REVERSE(SUBSTRING(address1, 1, 30))：取address1前30字符并反转，方便从末尾找第一个空格（也就是原字符串前30字符内的最后一个空格）
• CHARINDEX(' ', ...)：找到反转后字符串中第一个空格的位置，用30减去这个位置，得到原字符串中拆分点的索引
• LTRIM()：去除拆分后剩余部分的前导空格，避免拼接后出现多个空格
• 多步骤CTE：分步处理每个字段，确保上一步处理后的结果作为下一步的输入，逻辑清晰

示例验证 #

针对你给出的示例：

• 原address1：FLAT K 17TH FLOOR NO 100 NUDONG NORTH（长度约37）
• 处理后address1会截取到FLAT K 17TH FLOOR NO 100（长度28<30），剩余的NUDONG NORTH会追加到address2前，与原address2内容拼接成新的address2，再对新address2做同样的长度检查拆分。

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