Convert INSTR from Oracle to PostgreSQL

Oracle provides function INSTR(str1, str2, start_pos, n_occur) to search occurrence #n_occur of string str2 in the string str1 beginning at the start_pos position from head of the string. In case start_pos is negative, the function searches backwards. If n_occur is not specified, 1 is assumed by default that means search starts from the first position.

PostgreSQL does not have the same built-in function, so migration projects that relies on it must create custom implementation. This article contains example of how function INSTR can be implemented in PostgreSQL.

First, we create function accepting 2 arguments that just passes parameters to the 3-argument version of the function INSTR:

CREATE FUNCTION instr(varchar, varchar) 
RETURNS integer AS $$
DECLARE
    pos integer;
BEGIN
    pos := instr($1, $2, 1);
    RETURN pos;
END;
$$ LANGUAGE plpgsql;

Function INSTR that accepts 3 arguments can be implemented as follows:

CREATE FUNCTION instr(str1 varchar, str2 varchar, start_pos integer)
RETURNS integer AS $$
DECLARE
    pos integer NOT NULL DEFAULT 0;
    tmp varchar;
    beg integer;
    length integer;
    ss_length integer;
BEGIN
    IF start_pos > 0 THEN
        tmp := substring(str1 FROM start_pos);
        pos := position(str2 IN tmp);
        IF pos = 0 THEN
            RETURN 0;
        ELSE
            RETURN pos + start_pos - 1;
        END IF;
    ELSE
        ss_length := char_length(str2);
        length := char_length(str1);
        beg := length + start_pos - ss_length + 2;
        WHILE beg > 0 LOOP
            tmp := substring(str1 FROM beg FOR ss_length);
            pos := position(str2 IN tmp);
            IF pos > 0 THEN
                RETURN beg;
            END IF;
            beg := beg - 1;
        END LOOP;
        RETURN 0;
    END IF;
END;
$$ LANGUAGE plpgsql;

Finally, here is the most complicated version of the function INSTR that accepts 4 arguments with the same meaning as for original Oracle function:

CREATE FUNCTION instr(str1 varchar, str2 varchar,
                      start_pos integer, occur_index integer)
RETURNS integer AS $$
DECLARE
    pos integer NOT NULL DEFAULT 0;
    cnt_occur integer NOT NULL DEFAULT 0;
    tmp varchar;
    beg integer;
    i integer;
    length integer;
    ss_length integer;
BEGIN
    IF start_pos > 0 THEN
        beg := start_pos;
        tmp := substring(str1 FROM start_pos);
        FOR i IN 1..n_occur LOOP
            pos := position(str2 IN tmp);
            IF i = 1 THEN
                beg := beg + pos - 1;
            ELSE
                beg := beg + pos;
            END IF;
            tmp := substring(str1 FROM beg + 1);
        END LOOP;
        IF pos = 0 THEN
            RETURN 0;
        ELSE
            RETURN beg;
        END IF;
    ELSE
        ss_length := char_length(str2);
        length := char_length(str1);
        beg := length + start_pos - ss_length + 2;

        WHILE beg > 0 LOOP
            tmp := substring(str1 FROM beg FOR ss_length);
            pos := position(str2 IN tmp);
            IF pos > 0 THEN
                cnt_occur := cnt_occur + 1;
                IF cnt_occur = n_occur THEN
                    RETURN beg;
                END IF;
            END IF;
            beg := beg - 1;
        END LOOP;
        RETURN 0;
    END IF;
END;
$$ LANGUAGE plpgsql;

Have questions? Contact us