INSTR Function - Oracle to PostgreSQL Migration

In Oracle the INSTR function allows you to find the position of substring in a string. It accepts 2, 3 or 4 parameters.

PostgreSQL has the POSITION function, but it accepts 2 parameters only, so you have to use a user-defined function when converting Oracle INSTR with more than 2 parameters.

INSTR with 2 parameters searches the specified substring from the beginning of string:

Oracle:

  -- Find substring in string 
  SELECT INSTR('abc', 'b') FROM dual;
  # 2

In PostgreSQL you have to use the POSITION function. Note that order of parameters is different:

PostgreSQL:

  -- Find substring in string 
  SELECT POSITION('b' IN 'abc');
  # 2

INSTR with 3 parameters starts searching the specified substring from the specified position of string:

Oracle:

  -- Find substring in string starting from 3 position
  SELECT INSTR('abcb', 'b', 3) FROM dual;
  # 4

In PostgreSQL you have to use an user-defined function (see below). But there is one exception: if the start position is 1 you can still use POSTION function with 2 parameters:

  -- Find substring in string starting from 1 position
  SELECT INSTR('abcb', 'b', 1) FROM dual;
  # 2

PostgreSQL:

  -- We can skip the 3rd parameter in this case and still use POSITION
  SELECT POSITION('b' IN 'abc');
  # 2

INSTR with 4 parameters starts searching the specified substring from the specified position and returns the position of the specified occurrence of string:

Oracle:

  -- Find 2nd occurrence of substring in string starting from 3 position
  SELECT INSTR('abcbcb', 'b', 3, 2) FROM dual;
  # 6

In PostgreSQL you have to use an user-defined function to find the Nth occurrence of substring:

PostgreSQL:

  CREATE OR REPLACE FUNCTION instr4(p_str VARCHAR, p_substr VARCHAR, 
     p_start INT, p_occurrence INT)
  RETURNS integer AS $$
  DECLARE
    v_str   VARCHAR DEFAULT p_str;
    v_pos0  INT DEFAULT 0;
    v_pos   INT DEFAULT 0;
    v_found INT DEFAULT p_occurrence;
  BEGIN
    IF p_start >= 1 THEN
      v_str = SUBSTR(p_str, p_start);
      v_pos0 = p_start;
    END IF;
 
    WHILE 1=1 LOOP
	-- Find the next occurrence
	v_pos = POSITION(p_substr IN v_str);
 
	-- Nothing found
	IF v_pos IS NULL OR v_pos = 0 THEN
	  RETURN v_pos;
	END IF;
 
	-- The required occurrence found
	IF v_found = 1 THEN
	  EXIT;
	END IF;
 
	-- Prepare to find another one occurrence
	v_found := v_found - 1;
        v_pos0 := v_pos0 + v_pos;
	v_str := SUBSTR(v_str, v_pos);
    END LOOP;
 
    RETURN v_pos0 + v_pos;
  END;
  $$ LANGUAGE plpgsql STRICT IMMUTABLE;

So now you can use this user-defined function for INTR with 4 parameters in PostgreSQL:

PostgreSQL:

  -- Find 2nd occurrence of substring in string starting from 3 position
  SELECT INSTR4('abcbcb', 'b', 3, 2);
  # 6

Also some Oracle code can use INSTR function with 4 parameters, but 3rd and 4th parameters having values of 1. In this case the user-defined function is not required and POSITION function with 2 parameters can be used in PostgreSQL:

Oracle:

  -- 4 parameters are specified, but 3rd and 4th have default values
  SELECT INSTR('abcbcb', 'b', 1, 1) FROM dual;
  # 2

So this call can be converted to INSTR with 2 parameters in PostgreSQL:

PostgreSQL:

  SELECT POSITION('b' IN 'abcbcb');
  # 2

For more information, see Oracle to PostgreSQL Migration.