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.

CodePudding user response：

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*/   1
      then 0
      else
        length(array_to_string((
            string_to_array(substr(val /*string*/, 1 /*position*/), '$' /*substring*/)
          )[0:3/*occurrence*/],
          '$'/*substring*/)
        )   1
  end as instr
from tab

Postgres: fiddle
Oracle: fiddle