如何通过Oracle查询提取动态值0002970、0007702

针对格式为~STTM_CUSTOMER~[数值]~的字符串，可通过以下两种常用方法提取中间的数值（如示例中的0007702、0002970）：

方法一：使用SUBSTR + INSTR（适合固定格式场景） #

利用字符串定位函数确定数值的起止位置，精准截取：

SELECT 
  SUBSTR(your_column, 
         INSTR(your_column, '~', 1, 2) + 1, 
         INSTR(your_column, '~', 1, 3) - INSTR(your_column, '~', 1, 2) - 1) AS extracted_value
FROM your_table;

• INSTR(your_column, '~', 1, 2)：定位字符串中第2个~的位置
• 加1后作为SUBSTR的起始位置，再通过第3个~与第2个~的位置差计算数值长度，最终截取目标值

方法二：使用REGEXP_SUBSTR（灵活适配格式变动） #

通过正则表达式匹配并捕获数值部分，适配前缀固定、数值为连续数字的场景：

SELECT 
  REGEXP_SUBSTR(your_column, '~STTM_CUSTOMER~(\d+)~', 1, 1, NULL, 1) AS extracted_value
FROM your_table;

• 正则表达式~STTM_CUSTOMER~(\d+)~匹配指定前缀，(\d+)捕获连续数字的数值部分
• 最后一个参数1表示返回第一个捕获组的内容，即目标数值

提取单字段中的多个目标值 #

如果单条记录包含多个如~STTM_CUSTOMER~[数值]~的片段，可通过递归查询提取所有数值：

WITH sample_data AS (
  SELECT '~STTM_CUSTOMER~0007702~、~STTM_CUSTOMER~0002970~' AS target_str FROM dual
)
SELECT 
  REGEXP_SUBSTR(target_str, '~STTM_CUSTOMER~(\d+)~', 1, LEVEL, NULL, 1) AS extracted_value
FROM sample_data
CONNECT BY REGEXP_SUBSTR(target_str, '~STTM_CUSTOMER~(\d+)~', 1, LEVEL) IS NOT NULL;

该查询会返回两行结果，分别为0007702和0002970。

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