Converting function instr from Oracle to PostgreSQL (sql only)

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)

Example: instr(text, '$', 1, 3)

In PostgreSQL this does not exist, so I looked up an equivalent function (4 parameter is important).

I found: The function strpos(str, sub) in Postgres is equivalent of instr(str, sub) in Oracle. Tried options via split_part (it didn't work out). I need the same result only with standard functions Postgres (not own function). Maybe someone will offer options, even redundant in code.

Answer 1

This may be done in pure SQL using string_to_array.

with tab(val) as (
  select 'qwe$rty$123$456$78'
  union all
  select 'qwe$rty$123$'
  union all
  select '123$456$'
  union all
  select '123$456'
)
select
  val
  /*Oracle's signature: instr(string , substring [, position [, occurrence ] ])*/
  , case
      when
        array_length(
          string_to_array(substr(val /*string*/, 1 /*position*/), '$' /*substring*/),
          1
        ) <= 3 /*occurrence*/
      then 0
      else
        length(array_to_string((
            string_to_array(substr(val /*string*/, 1 /*position*/), '$' /*substring*/)
          )[:3/*occurrence*/],
          '$'/*substring*/)
        ) + 1
  end as instr
from tab

val	instr
qwe$rty$123$456$78	12
qwe$rty$123$	12
123$456$	0
123$456	0

Postgres: fiddle
Oracle: fiddle