Oracle SQL如何按~分隔符拆分列中可变长度的多段值？

方法1：固定分段数（用REGEXP_SUBSTR） #

如果列值的分段数固定（比如示例中的3段），直接用REGEXP_SUBSTR提取每一段即可：

SELECT
  full_address,
  -- 提取第1个分段
  REGEXP_SUBSTR(full_address, '[^~]+', 1, 1) AS address_line,
  -- 提取第2个分段
  REGEXP_SUBSTR(full_address, '[^~]+', 1, 2) AS city,
  -- 提取第3个分段
  REGEXP_SUBSTR(full_address, '[^~]+', 1, 3) AS country_code
FROM address_table;

参数说明：

• [^~]+：匹配所有非~的连续字符
• 第3个参数1：从字符串起始位置开始匹配
• 第4个参数：指定要提取的是第几个分段

方法2：动态分段数（用XMLTABLE） #

如果列值的分段数不固定，想要把每个分段拆成单独的行，用XMLTABLE结合Oracle的ora:tokenize函数：

SELECT
  t.full_address,
  x.split_segment
FROM address_table t,
     XMLTABLE(
       'ora:tokenize($str, "~")'
       PASSING t.full_address AS str
       COLUMNS split_segment VARCHAR2(1000) PATH '.'
     ) x;

这个语句会把每一行的分隔值拆成多行，每一行对应一个分段，不管原字段有多少个~分隔符。

特殊情况处理 #

如果字段存在首尾~或者连续~的情况（比如'~147 Spadina Ave~~Toronto~'），可以调整规则过滤空值：

• 对REGEXP_SUBSTR，把匹配规则改成'([^~]*)(~|$)'，配合NULLIF去掉空字符串
• 对XMLTABLE，在查询里加WHERE x.split_segment IS NOT NULL过滤空分段

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