Oracle编写字符串模式起止位置查询函数，INSTR函数是否适用

INSTR完全适合实现该需求，它支持指定查找起始位置、第N次匹配项的语法特性，刚好可以满足遍历字符串获取所有匹配项的要求。

• 每次调用INSTR从上次匹配结束的下一个位置开始查找下一个匹配项的起始位置
• 匹配成功时，结束位置 = 起始位置 + 搜索模式长度 - 1
• 循环执行直到INSTR返回0（无匹配项）即可收集到所有结果

首先修正你提供的测试表建表语句（原语句缺少id字段）：

CREATE TABLE data(
 id NUMBER,
 str VARCHAR2(100)
);

INSERT INTO data (id,str) VALUES (1,'123hellphello321hello64');

接下来创建自定义返回类型和核心函数：

-- 定义单条匹配结果的行结构
CREATE OR REPLACE TYPE match_result AS OBJECT (
 start_pos NUMBER,
 end_pos NUMBER,
 match_str VARCHAR2(100)
);
/
-- 定义结果集合的表结构
CREATE OR REPLACE TYPE match_result_tab AS TABLE OF match_result;
/

-- 全匹配查找函数
CREATE OR REPLACE FUNCTION get_all_matches(p_str VARCHAR2, p_pattern VARCHAR2)
RETURN match_result_tab PIPELINED
IS
 v_search_start NUMBER := 1;
 v_match_start NUMBER;
 v_pattern_len NUMBER := LENGTH(p_pattern);
BEGIN
 -- 入参合法性校验
 IF v_pattern_len = 0 OR p_str IS NULL THEN
  RETURN;
 END IF;
 LOOP
  -- 从指定位置开始查找第一个匹配项
  v_match_start := INSTR(p_str, p_pattern, v_search_start, 1);
  -- 无匹配项时退出循环
  EXIT WHEN v_match_start = 0;
  -- 输出当前匹配结果
  PIPE ROW(match_result(
   v_match_start,
   v_match_start + v_pattern_len - 1,
   p_pattern
  ));
  -- 下次查找从当前匹配位的下一位开始，避免重复匹配
  v_search_start := v_match_start + 1;
 END LOOP;
 RETURN;
END;
/

执行查询语句：

SELECT t.start_pos, t.end_pos, t.match_str AS str
FROM data d,
 TABLE(get_all_matches(d.str, 'hello')) t
WHERE d.id = 1;

返回结果和预期完全一致：

START_POS  END_POS STR
---------- ------- -----
9          13      hello
16         20      hello

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