从CLOB字段提取数据时如何指定结束字符获取目标值

方法一：使用INSTR和SUBSTR组合 #

原查询仅指定了截取起始位置，未限定结束位置，导致结果包含多余的"}。可以通过定位起始位置后第一个"的位置，截取两者间的内容：

select substr(to_char(work_request_details),
              start_pos,
              instr(to_char(work_request_details), '"', start_pos) - start_pos) as Company_Number
from (
    select work_request_details,
           instr(to_char(work_request_details), '"CompanyNumber":"') + 17 as start_pos
    from work_request
) t
where start_pos > 17; -- 过滤未找到目标字段的记录

逻辑说明 #

1. 子查询先计算目标值的起始位置："CompanyNumber":"的长度为17，起始位置为该字符串的匹配位置加17。
2. 外层查询通过INSTR从起始位置开始查找第一个"的位置，用该位置减去起始位置得到截取长度，最终提取出双引号间的纯数值。

方法二：使用正则表达式（更简洁） #

如果数据库支持正则表达式（如Oracle 10g及以上版本），可直接用REGEXP_SUBSTR提取匹配的数字部分：

select regexp_substr(to_char(work_request_details), '"CompanyNumber":"([0-9]+)"', 1, 1, null, 1) as Company_Number
from work_request;

逻辑说明 #

• 正则表达式"CompanyNumber":"([0-9]+)"匹配"CompanyNumber":"后接一串数字再跟"的格式。
• 最后一个参数1表示提取正则中第一个括号分组的内容，即目标数字部分，直接得到纯数值结果。

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