PostgreSQL中实现Oracle带4个参数的INSTRING函数等效功能的方法

一、POSITION函数参数支持说明 #

PostgreSQL原生的POSITION函数不支持类似Oracle INSTR的多参数传入，它仅支持基础的POSITION(子串 IN 原字符串)语法，只能返回子串第一次在原字符串中出现的位置，无法指定检索起始位置、第N次出现的匹配要求。

二、替代实现方案 #

方案1：使用regexp_instr函数（最便捷，PostgreSQL 10及以上版本支持） #

regexp_instr是PostgreSQL提供的正则匹配位置函数，参数逻辑和Oracle INSTR几乎完全兼容，你给出的示例INSTR(NAME,',',3,1)可以直接改写为：

regexp_instr(NAME, ',', 3, 1)

参数说明：

• 第1参数：原字符串
• 第2参数：要匹配的子串/正则表达式
• 第3参数：检索起始位置（和Oracle一致，正数从左数，负数从右数）
• 第4参数：第N次匹配的序号，示例中1就是第一次匹配

方案2：组合基础字符串函数（兼容所有PostgreSQL版本） #

如果你的数据库版本较低不支持regexp_instr，可以用strpos+substring组合实现，示例INSTR(NAME,',',3,1)改写逻辑如下：

CASE WHEN strpos(substring(NAME FROM 3), ',') > 0 
     THEN strpos(substring(NAME FROM 3), ',') + 2 
     ELSE 0 
END

逻辑说明：先截取原字符串从第3位开始的子串，查找逗号在子串中的位置，再加上前面跳过的2个字符，就是原字符串中的位置，没有匹配到返回0，和Oracle INSTR的返回逻辑一致。

方案3：自定义封装兼容INSTR的函数 #

如果需要完全和Oracle的INSTR用法对齐，可以直接创建自定义函数，后续直接调用即可：

CREATE OR REPLACE FUNCTION instr(string varchar, substring varchar, start_pos integer DEFAULT 1, nth integer DEFAULT 1)
RETURNS integer AS $$
DECLARE
    pos integer := 0;
    sub_pos integer;
    i integer := 0;
BEGIN
    IF start_pos > 0 THEN
        pos := start_pos - 1;
        WHILE i < nth LOOP
            sub_pos := strpos(substring(string FROM pos + 1), substring);
            IF sub_pos = 0 THEN
                RETURN 0;
            END IF;
            pos := pos + sub_pos;
            i := i + 1;
        END LOOP;
        RETURN pos;
    ELSE
        -- 处理从右侧开始检索的情况
        pos := length(string) + start_pos + 1;
        WHILE i < nth LOOP
            sub_pos := strpos(reverse(substring(string FROM 1 FOR pos)), reverse(substring));
            IF sub_pos = 0 THEN
                RETURN 0;
            END IF;
            pos := pos - sub_pos;
            i := i + 1;
        END LOOP;
        RETURN pos + 1;
    END IF;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

创建完成后就可以直接和Oracle一样调用instr(NAME,',',3,1)即可。

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