PRB: Behavior Change for SUBSTRING with Starting Position Less Than One

This article was previously published under Q310421
This article has been archived. It is offered "as is" and will no longer be updated.
SQL Server 2000 returns a non-null value when it calls the Transact-SQL SUBSTRING function if the value of the second parameter, the starting position, is less than one. Prior versions of SQL Server consider such a starting position as an out-of-range condition and return a NULL.
This problem occurs because the behavior in versions of SQL Server prior to SQL Server 2000 do not conform to the ANSI SQL-92 or SQL-99 specification. The change in SQL Server 2000 makes SQL Server compliant with the specified behavior of the standard.
This behavior is by design.
In typical usage scenarios, the starting position passed to SUBSTRING must be a one-based offset into the specified string expression. Prior versions of SQL Server incorrectly consider a value less than one to be an out-of-bounds condition and return a NULL.

In cases where the starting position is less than one, the ANSI specified behavior is to return a number of characters equal to the starting position plus length minus 1, starting at character position one. If the specified number of characters to return is less than one, the SUBSTRING function should return an empty string. Note that in this case that SUBSTRING always returns fewer characters than specified in the length parameter.

Consider the following examples:
---- SUBSTRING parameters-- SUBSTRING(string_expression, start, length)--SELECT SUBSTRING('abcd', 0, 1)    -- returns empty string ''SELECT SUBSTRING('abcd', 0, 2)    -- returns 'a'SELECT SUBSTRING('abcd', -4, 5)   -- returns empty string ''SELECT SUBSTRING('abcd', -4, 6)   -- returns 'a'				

Article ID: 310421 - Last Review: 01/17/2015 00:04:03 - Revision: 3.1

Microsoft SQL Server 2000 Standard Edition

  • kbnosurvey kbarchive kbprb KB310421