oracle SUBSTR to postgres SUBSTR function

Question

I have a function in oracle that i need converted to postgres.

i can't seem to find a reason for this difference in docs docs but:

oracle:

SELECT substr('1236',-4, 4) FROM DUAL;
    Result: 1236

postgres:

SELECT substr('1236',-4, 4);
    Result: empty (Null)

i need an output similiar to oracle and i cant seem to understand why the postgres function differs, and what i can use as an alternative

substr() doesn't accept a negative starting position in Postgres — user330315
– user330315, Commented Feb 24, 2021 at 12:19
It does accept, but shows different beheavior, select substr('string', -1, 3) returns st. I think it assumes you have something in -ve positions, and takes in account but returns only the valid values, so above returns select substr('string', 1, 1) — ZhengguanLi
– ZhengguanLi, Commented Feb 6, 2023 at 17:11

Gordon Linoff · Accepted Answer · 2021-02-24 12:22:58Z

4

I'm a little confused by your confusion. Oracle is quite clear that a negative position counts back from the end of the string.

Nothing in the Postgres documentation suggests that this behavior. There is no mention of negative positions (as far as I can tell) for any string functions other than left() and right(). And no hint whatsoever that negative positions have a special meaning in other contexts.

Postgres fortunately has a simpler way to do what you want:

select right('1236', 4)

answered Feb 24, 2021 at 12:22

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

oracle SUBSTR to postgres SUBSTR function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related