Oracle如何将(12345)some_string格式的字符串列拆分为两列

实现思路 #

直接使用Oracle内置正则匹配函数即可覆盖两类格式的拆分需求：

• 匹配开头括号内的数字串，匹配不到自动返回null，刚好满足无括号前缀场景的第一列输出要求
• 字符串剩余部分直接移除匹配到的(数字)前缀即可，匹配不到就返回原字符串，无需额外分支判断

11g及以上版本实现（推荐） #

假设待拆分的表名为your_table，原始字符串列名为src_col，拆分后两列分别命名为prefix_num、content_str，查询SQL如下：

SELECT
  REGEXP_SUBSTR(src_col, '^\((\d+)\)', 1, 1, NULL, 1) AS prefix_num,
  REGEXP_REPLACE(src_col, '^\(\d+\)', '') AS content_str
FROM your_table;

参数说明：

• REGEXP_SUBSTR的第6个参数1代表返回正则表达式第一个捕获组的内容，也就是括号内的数字部分，无匹配场景自动返回null
• REGEXP_REPLACE仅替换开头的(数字)格式串，无匹配场景直接返回原字符串

11g以下低版本兼容实现 #

如果使用的是不支持REGEXP_SUBSTR捕获组参数的低版本Oracle，可改用字符串截取函数实现：

SELECT
  CASE 
    WHEN src_col LIKE '(%' AND INSTR(src_col, ')') > 1 
    THEN SUBSTR(src_col, 2, INSTR(src_col, ')') - 2) 
  END AS prefix_num,
  CASE 
    WHEN src_col LIKE '(%' AND INSTR(src_col, ')') > 1 
    THEN SUBSTR(src_col, INSTR(src_col, ')') + 1) 
    ELSE src_col 
  END AS content_str
FROM your_table;

效果验证 #

你可以用以下测试SQL确认输出符合要求：

SELECT
  REGEXP_SUBSTR('(12345)some_string', '^\((\d+)\)', 1, 1, NULL, 1) AS prefix_num1,
  REGEXP_REPLACE('(12345)some_string', '^\(\d+\)', '') AS content_str1,
  REGEXP_SUBSTR('some_string', '^\((\d+)\)', 1, 1, NULL, 1) AS prefix_num2,
  REGEXP_REPLACE('some_string', '^\(\d+\)', '') AS content_str2
FROM dual;

返回结果：

• 带前缀的字符串拆分后：prefix_num1=12345，content_str1=some_string
• 无前缀的字符串拆分后：prefix_num2=null，content_str2=some_string

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