Oracle instr函数转PostgreSQL标准函数方案咨询（四参数版本）

直接用PostgreSQL标准内置函数，我们可以封装一个完全兼容Oracle INSTR(string, substring, starting point, nth location) 行为的自定义函数，或者用单次表达式实现特定场景。

一、封装自定义函数（推荐，迁移时直接替换） #

以下函数完全对齐Oracle INSTR的所有行为（包括正负起始位置、边界情况），仅使用PostgreSQL标准内置函数：

CREATE OR REPLACE FUNCTION instr(
    source_str text,
    sub_str text,
    start_pos integer DEFAULT 1,
    nth_occurrence integer DEFAULT 1
) RETURNS integer AS $$
DECLARE
    sub_len integer;
    source_len integer;
    adjusted_start text;
    match_pos integer;
BEGIN
    -- 处理空子串的Oracle兼容逻辑
    IF sub_str = '' THEN
        IF start_pos > 0 THEN
            RETURN LEAST(start_pos, LENGTH(source_str) + 1);
        ELSE
            RETURN GREATEST(LENGTH(source_str) + start_pos + 1, 1);
        END IF;
    END IF;
    -- nth参数为0直接返回0
    IF nth_occurrence <= 0 THEN
        RETURN 0;
    END IF;
    source_len := LENGTH(source_str);
    sub_len := LENGTH(sub_str);
    -- 空源字符串或子串长度为0返回0
    IF source_len = 0 OR sub_len = 0 THEN
        RETURN 0;
    END IF;

    -- 正向查找（起始位置为正）
    IF start_pos > 0 THEN
        adjusted_start := SUBSTRING(source_str FROM start_pos);
        SELECT (t.pos) INTO match_pos
        FROM unnest(regexp_positions(adjusted_start, regexp_escape(sub_str))) WITH ORDINALITY AS t(pos, len, idx)
        WHERE idx = nth_occurrence;
        IF match_pos IS NOT NULL THEN
            RETURN start_pos + match_pos - 1;
        ELSE
            RETURN 0;
        END IF;
    ELSE
        -- 反向查找（起始位置为负），通过反转字符串转为正向处理
        adjusted_start := REVERSE(SUBSTRING(source_str FROM source_len + start_pos + 1));
        SELECT (t.pos) INTO match_pos
        FROM unnest(regexp_positions(adjusted_start, regexp_escape(REVERSE(sub_str)))) WITH ORDINALITY AS t(pos, len, idx)
        WHERE idx = nth_occurrence;
        IF match_pos IS NOT NULL THEN
            RETURN source_len + start_pos + 1 + (LENGTH(adjusted_start) - match_pos + 1) - sub_len;
        ELSE
            RETURN 0;
        END IF;
    END IF;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

测试示例 #

• 你的需求场景：SELECT instr('a$b$c$d', '$', 1, 3); → 返回7（和Oracle结果一致）
• 反向查找：SELECT instr('a$b$c$d', '$', -1, 2); → 返回5（从右往左数第2个$的位置）
• 无匹配场景：SELECT instr('a$b$c$d', '$', 1, 4); → 返回0

二、单次表达式实现（无需创建函数） #

如果只是临时处理特定场景（比如你的示例），可以直接用以下表达式：

-- 示例：查找第3次出现的$的位置
SELECT COALESCE(
    (SELECT pos 
     FROM unnest(regexp_positions('a$b$c$d', regexp_escape('$'))) 
     WITH ORDINALITY AS t(pos, len, idx) 
     WHERE idx = 3),
    0
);

关键说明 #

• regexp_escape用于转义子串中的正则特殊字符（如., *），避免匹配逻辑出错
• 所有用到的函数（regexp_positions, unnest, reverse, substring等）都是PostgreSQL标准内置函数，无需安装扩展
• 函数完全兼容Oracle的边界规则（包括空串、起始位置超出字符串长度、nth参数为0等场景）

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