Oracle INSTR function returns the position of a substring in a string and can have following syntax.

INSTR(string, substring)
INSTR(string, substring, start_position)
INSTR(string, substring, start_position, occurrence)

PostgreSQL database does not have INSTR function, but has STRPOS function which works in a similar way with two arguments which means INSTR(string, substring) can be converted as STRPOS(string, substring) if the number of arguments is 2.

Have a look at the examples to verify the result when you have 2 arguments.

SQL> SELECT instr('This is testing', 'i') res FROM DUAL;

       RES
----------
	 3


SQL> SELECT instr('This is testing', 'is') res FROM DUAL;

       RES
----------
	 3

postgres=# SELECT strpos('This is testing', 'i') res;
 res 
-----
   3
(1 row)

postgres=# SELECT strpos('This is testing', 'is') res;
 res 
-----
   3
(1 row)

However, if you are migrating INSTR function that contains 3 or more parameters, then PostgreSQL throws errors.

--Oracle
SQL> SELECT instr('This is testing', 'i', 1) res FROM DUAL;

       RES
----------
	 3


--PostgreSQL
postgres=# SELECT strpos('This is testing', 'i', 1) res;
ERROR:  function strpos(unknown, unknown, integer) does not exist
LINE 1: SELECT strpos('This is testing', 'i', 1) res;
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

In this case, you have to write custom user defined function called as wrapper function in PostgreSQL to achieve Oracle instr functionality. Because instr is a generic function and is one pain area in every migration project, we have built a wrapper function which will behave exactly same as Oracle instr function and can also accept the same number of arguments.

Introducing INSTR wrapper function in PostgreSQL

CREATE OR REPLACE FUNCTION instr(
    IN _value CHARACTER VARYING, 
    IN _str CHARACTER VARYING, 
    IN _pos INTEGER DEFAULT 1, 
    IN _num INTEGER DEFAULT 1)
RETURNS INTEGER
AS $$
DECLARE
  _is_reversed bool := FALSE;
  _idx INTEGER := 0;
  _offset INTEGER;
  _i INTEGER;

BEGIN

   IF (_num < 0) THEN
      RAISE exception 'Invalid parameter';
   END IF;
  
   _offset := LENGTH(_str);

   IF (_pos < 1) THEN 
     IF (_pos < 0) THEN
       _value := reverse(_value);
       _str := reverse(_str);
       _is_reversed := true;
       _pos := ABS(_pos);
     ELSE 
       _pos := 1;
     END IF;
   END IF; 

   FOR _i IN 1.._num
   LOOP
     _idx := strpos(substr(_value, _pos), _str);
     _pos := _pos + _idx + _offset;
     EXIT WHEN _idx = 0;
   END LOOP;

   IF (_idx > 0) THEN
     IF (_is_reversed) THEN
       -- Why -3: _pos is one based, reverse, and length of _str
       _idx := LENGTH(_value) - ((_pos - _offset - 3) + _offset);
     ELSE 
       _idx := _pos - _offset - 1;
     END IF;
   END IF;

   RETURN _idx;

END;
$$ language plpgsql
  immutable returns null on null input;

Testing the wrapper function

Let’s test the wrapper function created above with the same parameters in both the environment.

SQL> SELECT instr('This is testing', 'i', 1) res FROM DUAL;

       RES
----------
	 3

SQL> SELECT instr('This is testing', 'i', 1, 2) res FROM DUAL;

       RES
----------
	 6

SQL> SELECT instr('This is testing', 'is', -1, 2) res FROM DUAL;

       RES
----------
	 3

SQL> SELECT instr('This is testing', 'is', -1, -2) res FROM DUAL;
SELECT instr('This is testing', 'is', -1, -2) res FROM DUAL
                                                       *
ERROR at line 1:
ORA-01428: argument '-2' is out of range

postgres=# SELECT instr('This is testing', 'i', 1) res;
 res 
-----
   3
(1 row)

postgres=# SELECT instr('This is testing', 'i', 1, 2) res;
 res 
-----
   6
(1 row)

postgres=# SELECT instr('This is testing', 'is', -1, 2) res;
 res 
-----
   3
(1 row)

postgres=# SELECT instr('This is testing', 'is', -1, -2) res;
ERROR:  Invalid parameter
CONTEXT:  PL/pgSQL function instr(character varying,character varying,integer,integer) line 11 at RAISE

Take Aways