Oracle SQL拆分房屋编号与附加信息问题求助

原SQL的核心错误 #

1. 正则表达式逻辑完全颠倒：
   • 提取房屋编号（Housenumber）时，原语句用[^0-9,]，这会匹配非数字、非逗号的字符，和需求的提取数字完全相反。
   • 提取附加信息（Addition）时，原语句用[^A-Z,]，会匹配非大写字母、非逗号的字符，同样不符合需求。
2. 定义的TEST_DATA公共表达式（CTE）未被使用，属于冗余代码。
3. 正则未考虑字母大小写（如果数据中有小写字母，原语句会匹配不到）。

修正后的SQL语句 #

SELECT 
    Houseid,
    Streetid,
    Gemeindeschl,
    Gemeinde,
    Bundesland,
    Landkreis,
    REGEXP_SUBSTR(t.Ort, '[^,]+', 1, 1) Ort,
    REGEXP_SUBSTR(t.Ort, '[^,]+', 1, 2) Ortszusatz,
    Strasse,
    -- 提取连续数字作为房屋编号
    REGEXP_SUBSTR(t.Nummer, '[0-9]+', 1, 1) Housenumber,
    -- 提取单个字母作为附加信息（兼容大小写）
    REGEXP_SUBSTR(t.Nummer, '[A-Za-z]', 1, 1) Addition,
    Objektkl,
    Lng,
    Lat,
    Plz
FROM adresses T
-- 如果原表存在重复记录，可添加DISTINCT去重
-- DISTINCT

关键逻辑说明 #

• REGEXP_SUBSTR(t.Nummer, '[0-9]+', 1, 1)：匹配Nummer字段中第一个连续的数字序列，完美适配"16f""234B"这类格式。
• REGEXP_SUBSTR(t.Nummer, '[A-Za-z]', 1, 1)：匹配Nummer字段中第一个字母字符，兼容大小写；如果只需要匹配大写字母，可改为[A-Z]。
• 若Nummer字段只有数字（无附加信息），Addition字段会返回NULL，符合业务逻辑。

关于条目重复的问题 #

如果执行后仍有重复条目，大概率是adresses表本身存在重复记录：

• 检查Houseid等主键字段是否唯一，排查数据重复的根源。
• 临时解决可在SELECT后添加DISTINCT关键字，过滤重复行。

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