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.

position ( 'substring' IN substring ( 'string' FROM start FOR count)) — Edouard, Oct 27 '22 at 07:36

astentx · Accepted Answer · 2022-10-27T14:05:39.033

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

Converting function instr from Oracle to PostgreSQL (sql only)

1 Answers1