Converting function from Oracle to PostgreSQL

Question

I am working on converting something from Oracle to PostgreSQL. In the Oracle file there is a function:

instr(string,substring,starting point,nth location)

or as it is in my file

instr(string,chr(10),instr(string,substring),1)

In PostgreSQL this does not exist, so I looked up an equivalent function. I found:

position(substring in string)

but this does not allow the starting position and the nth location parameters.

Is there anyway to make this function start at a given point? Or is there a better function to use in PostgreSQL where I can specify starting position and the nth location?

This would have to work on PostgreSQL 8.2.15 because that is the version we are running on the database.

There is substring(string [from int] [for int]) if you need to operate on part of the string. Perhaps there's easier solution, but you didn't show us your input or expected output. — tvm
– tvm, Commented Jun 25, 2015 at 22:37
Are you sure about version 8.2.15? 8.2 is EOL since December 2011, and you're also missing 8 minor versions: The latest 8.2 version was 8.2.23. Your version has many serious (security) issues. Do yourself a favor and upgrade to a recent version asap. — Frank Heikens
– Frank Heikens, Commented Jun 26, 2015 at 5:42

klin · Accepted Answer · 2024-12-28 13:37:25Z

25

The function strpos(str, sub) in Postgres is equivalent of instr(str, sub) in Oracle. Unfortunately, the function does not have third and fourth parameters, so the expression in Postgres must be more complex.

The function substr(str, n) gives a substring of str starting from n position.

instr(str, ch, instr(str, sub), 1);                               --oracle
strpos(substr(str, strpos(str, sub)), ch) + strpos(str, sub) - 1; --postgres

As instr() is a powerful function I wrote it in plpgsql for my own needs.

create or replace function instr(str text, sub text, startpos int = 1, occurrence int = 1)
returns int language plpgsql immutable
as $$
declare 
    tail text;
    shift int;
    pos int;
    i int;
begin
    shift:= 0;
    if startpos = 0 or occurrence <= 0 then
        return 0;
    end if;
    if startpos < 0 then
        str:= reverse(str);
        sub:= reverse(sub);
        pos:= -startpos;
    else
        pos:= startpos;
    end if;
    for i in 1..occurrence loop
        shift:= shift+ pos;
        tail:= substr(str, shift);
        pos:= strpos(tail, sub);
        if pos = 0 then
            return 0;
        end if;
    end loop;
    if startpos > 0 then
        return pos+ shift- 1;
    else
        return length(str)- length(sub)- pos- shift+ 3;
    end if;
end $$;

Some checks (Examples from OLAP DML Functions):

select instr('Corporate Floor', 'or', 3, 2);  -- gives 14
select instr('Corporate Floor', 'or', -3, 2); -- gives 2

There is no reverse() function in Postgres 8.2. You can use this:

-- only for Postgres 8.4 or earlier!
create or replace function reverse(str text)
returns text language plpgsql immutable
as $$
declare
    i int;
    res text = '';
begin
    for i in 1..length(str) loop
        res:= substr(str, i, 1) || res;
    end loop;
    return res;
end $$;

Note. Postgres 15 introduced the regexp_instr() function.

regexp_instr ( string text, pattern text [, start integer [, N integer [, endoption integer [, flags text [, subexpr integer ] ] ] ] ] ) → integer

edited Dec 28, 2024 at 13:37

answered Jun 26, 2015 at 2:25

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ice2burn Over a year ago

instr function not correct, care if you copy it: instr('1aba2bab3', 'aba', -1) results 3 should be 2; instr('1aba2bab3', 'ab', -1) results 7 - correct; instr('1aba2bab3', 'b', -1) results 7 should be 8; instr('1aba2bab3', '3', -1) results 8 should be 9;

klin Over a year ago

@Ice2burn - thanks, there was a mistake in the last return, corrected.

Ice2burn Over a year ago

All good now, thank you. Was looking for RightPos function, your solution was the only one I've found

Patrick · Accepted Answer · 2015-06-26 02:40:41Z

The simplest form:

instr(string, substring) ::= strpos(string, substring)

With a position parameter:

For a positive position value:

instr(string, substring, position) ::= strpos(substr(string, position), substring) + position - 1

For a negative position value:

instr(string, substring, position) ::= strpos(substr(string, char_length(string) + position + 1), substring) + char_length(string) + position

With an occurrence parameter:

This does not exist in PostgreSQL. You don't seem to need it (example gives occurrence = 1) but if you do then you need to write a function that recursively works on sub-strings extracted from the second version.

So:

instr(string,chr(10),instr(string,substring),1)

becomes

strpos(substr(string, strpos(string, substring)), chr(10)) + strpos(string, substring) - 1

leonidv · Accepted Answer · 2020-11-06 21:56:24Z

1

You can use split_part() function in postgres

Refer to the following link

https://www.postgresqltutorial.com/postgresql-split_part/

SELECT SPLIT_PART('A,B,C', ',', 2);

edited Nov 6, 2020 at 21:56

leonidv

1,42216 silver badges20 bronze badges

answered Nov 6, 2020 at 6:51

Krishna

111 bronze badge

Collectives™ on Stack Overflow

Converting function from Oracle to PostgreSQL

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related