INSTR Function - Oracle to PostgreSQL Migration

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

Oracle's INSTR function also supports a negative starting position, meaning that it is counted backward from the end of the string and the search is performed backward from the resulting position.

In PostgreSQL, you can use the STRPOS and POSITION functions, but they accept 2 parameters only, so you have to use a user-defined function when converting Oracle INSTR with more than 2 parameters.

PostgreSQL also provides the REGEXP_INSTR function, which allows you to specify the starting position and occurrence; however, it does not support a negative starting position, and you may need to modify the pattern to escape regular-expression special characters.

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

Oracle:

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

In PostgreSQL you can use the STRPOS, POSITION and REGEXP_INSTR functions:

PostgreSQL:

  -- Find substring in string 
  SELECT STRPOS('abc', 'b');
  /* 2 */
 
  -- Note that POSITION uses the different order of parameters
  SELECT POSITION('b' IN 'abc');
  /* 2 */
 
  SELECT REGEXP_INSTR('abc', 'b');
  /* 2 */

INSTR with 3 parameters starts searching for the specified substring from the specified position of string. The start position can be negative, which is often used to find the last occurrence:

Oracle:

  -- Find substring in string starting from 3 position
  SELECT INSTR('ab.cb.', 'b.', 3) FROM dual;
  /* 5 */
 
  -- Use negative start position to find the last occurrence of '.'
  SELECT INSTR('Document.Draft.pdf', '.', -1) FROM dual;
  /* 15 */

In PostgreSQL you can use an user-defined function (see below), REGEXP_INSTR or an expression for specific use cases:

PostgreSQL:

  -- Use REGEXP_INSTR if the starting position is positive! Note that . is escaped with \. now
  SELECT REGEXP_INSTR('ab.cb.', 'b\.', 3);   
  /* 5 */
 
  -- Use STRPOS and SUBSTR to search from a specified starting position (returns 0 if not found)
  SELECT (STRPOS(SUBSTR('ab.cb.', 3), 'b.') + 3 - 1) * (STRPOS(SUBSTR('ab.cb.', 3), 'b.') > 0)::INT;
  /* 5 */
 
  -- Expression to implement backward search (returns 0 if not found)
  SELECT (CHAR_LENGTH('Document.Draft.pdf') - STRPOS(REVERSE('Document.Draft.pdf'), '.') + 1)
    * (STRPOS('Document.Draft.pdf', '.') > 0)::INT;
  /* 15 */

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 */
 
  -- Search for 3nd occurrence backward!
  SELECT INSTR('abcbcb', 'b', -3, 2) FROM dual;
  /* 2 */

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

PostgreSQL:

  CREATE OR REPLACE FUNCTION SQLINES_INSTR(p_str VARCHAR, p_substr VARCHAR, 
     p_start INT, p_occurrence INT)
  RETURNS integer AS $$
  DECLARE
    v_str   VARCHAR DEFAULT p_str;
    v_substr   VARCHAR DEFAULT p_substr;
    v_pos0  INT DEFAULT p_start;
    v_pos   INT DEFAULT 0;    
    v_found INT DEFAULT p_occurrence;
  BEGIN
    -- Search backward
    IF p_start < 0 THEN
      v_str := REVERSE(p_str);
      v_substr := REVERSE(p_substr);
      v_pos0 := v_pos0 * -1;
    END IF;  
 
    WHILE 1=1 LOOP
      -- Find the next occurrence
      v_pos = POSITION(v_substr IN SUBSTR(v_str, v_pos0));
 
      -- 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
        v_pos := v_pos0 + v_pos - 1;
        EXIT;
      END IF;
 
      -- Prepare to find another one occurrence
      v_found := v_found - 1;
      v_pos0 := v_pos0 + v_pos;      
    END LOOP;
 
    -- Calculate the resulting position if backward search was performed
    IF p_start < 0 AND v_pos > 0 THEN
       v_pos := CHAR_LENGTH(p_str) - v_pos + 1; 
    END IF;
 
    RETURN v_pos;
  END;
  $$ LANGUAGE plpgsql STRICT IMMUTABLE;

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

PostgreSQL:

  -- Find 2nd occurrence of substring in string starting from 3 position
  SELECT SQLINES_INSTR('abcbcb', 'b', 3, 2);
  /* 6 */
 
  -- Search for 2nd occurrence backward!
  SELECT SQLINES_INSTR('abcbcb', 'b', -3, 2);
  /* 2 */

If Oracle's INSTR uses 4 parameters but the start position is 1 and the occurrence is also 1, you can use PostgreSQL's STRPOS with 2 parameters since these values are the defaults in PostgreSQL:

Oracle:

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

We can use STRPOS with 2 parameters in PostgreSQL:

PostgreSQL:

  SELECT STRPOS('abcbcb', 'b');
  /* 2 */

For more information, see Oracle to PostgreSQL Migration.