INSTR Function - Oracle to SQL Server Migration

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

SQL Server provides the CHARINDEX function to find the position of substring, but it accepts only 2 or 3 parameters, so you have to use an user-defined function when converting Oracle INSTR with 4 parameters.

Also note that the order of parameters in Oracle INSTR and SQL Server CHARINDEX is different.

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 SQL Server you can use CHARINDEX and change the order of parameters.

SQL Server:

  -- Find substring in string 
  SELECT CHARINDEX('b', '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 SQL Server you still can use the CHARINDEX function by changing the order of first 2 parameters.

SQL Server:

  -- Find substring in string starting from 3 position
  SELECT CHARINDEX('b', 'abcb', 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

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

SQL Server:

  CREATE FUNCTION INSTR4 (@p_str VARCHAR(8000), @p_substr VARCHAR(255), 
     @p_start INT, @p_occurrence INT)
  RETURNS INT
  BEGIN
    DECLARE @v_found INT = @p_occurrence;
    DECLARE @v_pos INT = @p_start;
 
    WHILE 1=1 
    BEGIN
	  -- Find the next occurrence
	  SET @v_pos = CHARINDEX(@p_substr, @p_str, @v_pos);
 
	  -- Nothing found
	  IF @v_pos IS NULL OR @v_pos = 0
	    RETURN @v_pos;
 
	  -- The required occurrence found
	  IF @v_found = 1
            BREAK;
 
	  -- Prepare to find another one occurrence
	  SET @v_found = @v_found - 1;
	  SET @v_pos = @v_pos + 1;
    END;
 
    RETURN @v_pos;
  END
  GO
 
  GRANT EXEC ON dbo.INSTR4 TO PUBLIC
  GO

Now you can use this user-defined function for INTR with 4 parameters in SQL Server:

SQL Server:

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

Also some Oracle code can use INSTR function with 4 parameters, but 4th parameter having value of 1. In this case the user-defined function is not required and CHARINDEX function with 3 parameters can be used in SQL Server:

Oracle:

  -- 4 parameters are specified, but 4th has default value
  SELECT INSTR('abcbcb', 'b', 2, 1) FROM dual;
  # 2

So this call can be converted to CHARINDEX with 3 parameters in SQL Server:

SQL Server:

  SELECT CHARINDEX('b', 'abcbcb', 2);
  # 2

For more information, see Oracle to SQL Server Migration.