SUBSTR Function - Oracle to SQL Server Migration

In Oracle, the SUBSTR function returns a substring from a string, starting at the specified position (which can be negative) and for the specified length, or until the end of the string if no length is provided.

In SQL Server, you can use the SUBSTRING function, but it does not allow a negative start position with the same meaning as in Oracle, 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 */

In Oracle, if the length is not specified, the substring extends to the end of the string. In SQL Server, the length must be specified, and you can use the LEN function for the length parameter:

Oracle:

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

SQL Server:

  -- Get substring from position 5 until the end of string
  SELECT SUBSTRING('New York', 5, LEN('New York'));
  /* York */

In Oracle, if the start position is negative the SUBSTR function starts counting from the end of the string. In SQL Server, you can use the 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:

  -- Get 3 characters from position 4 counting from the end of string
  SELECT SUBSTRING('New York', LEN('New York') + 1 - 4, 3);
  /* Yor */

If a negative start position is specified, but the length is omitted, you can also use the 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 */

In Oracle, a start position of 0 is treated as 1, whereas in SQL Server, it is handled differently:

Oracle:

  -- A start position of 0 is treated as 1  
  SELECT SUBSTR('New York', 0, 3) FROM dual;
  /* New */

SQL Server:

  -- A start position of 0 affects the length of the substring
  SELECT SUBSTRING('New York', 0, 3);
  /* Ne */

Note that if start is less than 1 (can be also negative), SQL Server calculates the substring length as start + length - 1.

For more information, see Oracle to SQL Server Migration.