Article ID: 310421 - View products that this article applies to.
This article was previously published under Q310421
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.
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: