SUBSTR - Get Substring from String - Oracle to SQL Server Migration

In Oracle, SUBSTR function returns the substring from a string starting from the specified position and having the specified length (or until the end of the string, by default).

In SQL Server, you can use SUBSTRING function, but it does not allow you to specify a negative start position, and the substring length must be specified .

Oracle:

  -- Get first 3 characters
  SELECT SUBSTR('New York', 1, 3) FROM dual;
  # New
 
  -- Get last 4 characters (negative start position)
  SELECT SUBSTR('New York', -4) FROM dual;
  # York

SQL Server:

  -- Get first 3 characters
  SELECT SUBSTRING('New York', 1, 3);
  # New
 
  -- Get last 4 characters 
  SELECT RIGHT('New York', 4);
  # York

Summary information:

	Oracle	SQL Server
Syntax	SUBSTR(string, start [, length])	SUBSTRING(string, start, length)
Negative Start Position	start counted from the end of string
Length	Optional, until the end of string by default	Must be specified
Alternatives		LEFT function if start is 1
		RIGHT function if length is not specified

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

If Oracle SUBSTR function is used with a positive start position, and the length is specified you can convert it to SUBSTRING in SQL Server:

Oracle:

  -- Get 4 characters starting from position 5
  SELECT SUBSTR('New York', 5, 4) FROM dual;
  # York

SQL Server:

  -- Get 4 characters starting from position 5
  SELECT SUBSTRING('New York', 5, 4);
  # York

In Oracle, if the length is not specified, the substring is taken from the start position until the end of the string. In SQL Server, the length must be specified, and you can use LEN function to provide the length parameter:

Oracle:

  -- Get substring from position 5 until the end of string
  SELECT SUBSTR('New York', 5) FROM dual;
  # York

SQL Server:

  DECLARE @str VARCHAR(10) = 'New York';
 
  -- Get substring from position 5 until the end of string
  SELECT SUBSTRING(@str, 5, LEN(@str));
  # York

Additionally, you can use RIGHT function to convert Oracle SUBSTR used without the length:

SQL Server:

  DECLARE @str VARCHAR(10) = 'New York';
 
  -- Get substring from position 5 until the end of string
  SELECT RIGHT(@str, LEN(@str) + 1 - 5);
  # York

In Oracle, if the start position is negative SUBSTR function calculates it from the end of the string. In SQL Server, you can use SUBSTRING and LEN functions:

Oracle:

  -- Get 3 characters from position 4 counting from the end of string
  SELECT SUBSTR('New York', -4, 3) FROM dual;
  # Yor

SQL Server:

  DECLARE @str VARCHAR(10) = 'New York';
 
  -- Get 3 characters from position 4 counting from the end of string
   SELECT SUBSTRING(@str, LEN(@str) + 1 - 4, 3);
  # Yor

If the negative start position is specified, but the length is omitted, you can also use RIGHT function in SQL Server:

Oracle:

  -- Get last 4 characters in string
  SELECT SUBSTR('New York', -4) FROM dual;
  # York

SQL Server:

  -- Get last 4 characters in string
  SELECT RIGHT('New York', 4);
  # York

You can also use RIGHT function if the absolute values of the negative start position and length are equal:

Oracle:

  -- Get last 4 characters in string
  SELECT SUBSTR('New York', -4, 4) FROM dual;
  # York

SQL Server:

  -- Get last 4 characters in string
  SELECT RIGHT('New York', 4);
  # York

• Oracle to SQL Server Migration Knowledge Base

Oracle 11g R2 SQL Language Reference

• SUBSTR Function

Microsoft SQL Server 2012 - Books Online

• SUBSTRING Function
• RIGHT Function
• LEFT Function

SQLines offers services to migrate Oracle databases and applications to Microsoft SQL Server. For more information, please Contact Us.