如何在SQL中将街道与门牌号拆分至独立列

需求说明 #

数据库O的Table A包含strasse（街道）和hausnummer（门牌号）列，数据库B的Table B仅含strasse列，需将Table B的strasse字段拆分为街道和门牌号后导入Table A。例如Table B中strasse值为Examplestreet 5，需拆分为Table A的strasse=Examplestreet、hausnummer=5。

现有语句错误分析 #

第一个语句问题 #

SELECT 
     LEFT(Strasse, LEN(Strasse) - CHARINDEX(' ', REVERSE(Strasse) + ' ')) AS strasse,
    RIGHT(Strasse, CHARINDEX(' ', REVERSE(Strasse) + ' ') - 1) AS hausnummer
FROM TABLE

• 错误原因：REVERSE(Strasse) + ' '额外添加的空格会导致位置计算偏移，若原字符串末尾无空格，该空格会被识别为最后一个分隔符，拆分出的strasse会带末尾空格；当街道名称本身含空格（如Main Street 5）或门牌号不在字符串末尾时，会错误将街道内容划入hausnummer。

第二个语句问题 #

SELECT
    LTRIM(RTRIM(REPLACE(Strasse, 
                        CASE 
                            WHEN PATINDEX('%[0-9]%', Strasse) = 0
                                THEN ''
                            ELSE SUBSTRING(Strasse, PATINDEX('%[0-9]%', Strasse), LEN(Strasse) - PATINDEX('%[0-9]%', Strasse) + 1)
                        END, ''))) AS strasse,
    CASE 
        WHEN PATINDEX('%[0-9]%', Strasse) = 0
            THEN ''
        ELSE SUBSTRING(Strasse, PATINDEX('%[0-9]%', Strasse), LEN(Strasse) - PATINDEX('%[0-9]%', Strasse) + 1)
    END AS hausnummer
FROM
    TABLE

• 错误原因：PATINDEX('%[0-9]%', Strasse)会匹配字符串中第一个数字，若街道名称包含数字（如1st Street 5），会从该数字位置开始截取，导致strasse丢失部分内容、hausnummer包含无关字符；若数据库不支持PATINDEX的正则匹配语法，则会返回0，导致hausnummer无数据。

针对性解决方案 #

方案1：按末尾空格拆分（适用于门牌号在字符串末尾、与街道用空格分隔的场景） #

通过反向查找最后一个空格的位置拆分，同时处理无空格的异常情况：

-- 先验证拆分结果
SELECT
    CASE 
        WHEN CHARINDEX(' ', b.Strasse) = 0 THEN LTRIM(RTRIM(b.Strasse))
        ELSE LTRIM(RTRIM(LEFT(b.Strasse, LEN(b.Strasse) - CHARINDEX(' ', REVERSE(b.Strasse)))))
    END AS strasse,
    CASE 
        WHEN CHARINDEX(' ', b.Strasse) = 0 THEN ''
        ELSE LTRIM(RTRIM(RIGHT(b.Strasse, CHARINDEX(' ', REVERSE(b.Strasse)))))
    END AS hausnummer
FROM [DatabaseB].[dbo].[TableB] b

-- 验证无误后执行导入
INSERT INTO [DatabaseO].[dbo].[TableA] (strasse, hausnummer)
SELECT
    CASE 
        WHEN CHARINDEX(' ', b.Strasse) = 0 THEN LTRIM(RTRIM(b.Strasse))
        ELSE LTRIM(RTRIM(LEFT(b.Strasse, LEN(b.Strasse) - CHARINDEX(' ', REVERSE(b.Strasse)))))
    END AS strasse,
    CASE 
        WHEN CHARINDEX(' ', b.Strasse) = 0 THEN ''
        ELSE LTRIM(RTRIM(RIGHT(b.Strasse, CHARINDEX(' ', REVERSE(b.Strasse)))))
    END AS hausnummer
FROM [DatabaseB].[dbo].[TableB] b

方案2：提取末尾数字/字母组合（适用于门牌号为末尾数字+可选字母，如5a、12b） #

若门牌号可能包含字母后缀，可结合正则提取（以SQL Server 2017+为例）：

SELECT
    LTRIM(RTRIM(REGEXP_REPLACE(b.Strasse, '[0-9][a-zA-Z]*$', ''))) AS strasse,
    LTRIM(RTRIM(CASE 
        WHEN PATINDEX('%[0-9]%', b.Strasse) > 0 THEN SUBSTRING(b.Strasse, PATINDEX('%[0-9]%', b.Strasse), LEN(b.Strasse))
        ELSE ''
    END)) AS hausnummer
FROM [DatabaseB].[dbo].[TableB] b

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