postgresqlinstr

Converting function instr from Oracle to PostgreSQL (sql only)


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.


Solution

  • 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