INSTR - Find Position in String - Oracle to SQL Server Migration

In Oracle, INSTR function returns the position of a substring in a string, and allows you to specify the start position and which occurrence to find.

In SQL Server, you can use CHARINDEX function that allows you to specify the start position, but not the occurrence, or you can use a user-defined function.

Oracle Example:

  -- Find position of word York
  SELECT INSTR('New York', 'York', 1) FROM dual;
  -- Result: 5

SQL Server Example:

  -- Find position of word York
  SELECT CHARINDEX( 'York', 'New York', 1);
  -- Result: 5

Note the different order of the parameters in INSTR and CHARINDEX

Oracle INSTR to SQL Server conversion summary:

	Oracle	SQL Server
Syntax	INSTR(string, substring [, start [, occurrence]])	CHARINDEX(substring, string [, start]
Start Position
Negative Start Position
Occurrence

Last Update: Oracle 11g R2 and Microsoft SQL Server 2012

In many cases, if the start position is not negative and you look for the first occurrence, you can convert Oracle INSTR function to CHARINDEX in SQL Server (just note that the parameter order is different )

Oracle INSTR allows you to find the second, the third etc. occurrence of a substring in a string:

Oracle:

  -- Find the second occurrence of letter 'o'
  SELECT INSTR('Boston', 'o', 1, 2) FROM dual;
  -- Result: 5

If you need to find the second, the third etc. occurrence of a substring in a string in SQL Server, you can use a user-defined function:

SQL Server:

  -- Drop the function if it already exists
  IF OBJECT_ID('INSTR', 'FN') IS NOT NULL
	DROP FUNCTION INSTR
  GO
 
  -- User-defined function to implement Oracle INSTR in SQL Server
  CREATE FUNCTION INSTR (@str VARCHAR(8000), @substr VARCHAR(255), @start INT, @occurrence INT)
  RETURNS INT
  AS
  BEGIN
	DECLARE @found INT = @occurrence,
			@pos INT = @start;
 
	WHILE 1=1 
	BEGIN
		-- Find the next occurrence
		SET @pos = CHARINDEX(@substr, @str, @pos);
 
		-- Nothing found
		IF @pos IS NULL OR @pos = 0
			RETURN @pos;
 
		-- The required occurrence found
		IF @found = 1
			BREAK;
 
		-- Prepare to find another one occurrence
		SET @found = @found - 1;
		SET @pos = @pos + 1;
	END
 
	RETURN @pos;
  END
  GO

Now you can find the required occurrence in SQL Server:

SQL Server:

  -- Find the second occurrence of letter 'o'
  SELECT dbo.INSTR('Boston', 'o', 1, 2);
  -- Result: 5

Note that you have to specify a schema qualifier to call an user-defined function in SQL Server