INSTR Function - Oracle to Snowflake Migration

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

In Snowflake you have to use the REGEXP_INSTR function that also accepts 2, 3 or 4 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

Snowflake:

  -- Find substring in string 
  SELECT REGEXP_INSTR('abc', 'b');
  # 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

Snowflake:

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

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

Snowflake:

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

For more information, see Oracle to Snowflake Migration.