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


The information in this article applies to:

  • Microsoft SQL Server 2000 (all editions)
This article was previously published under Q310421

SYMPTOMS

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.

CAUSE

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.

STATUS

This behavior is by design.

MORE INFORMATION

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'
Modification Type:MajorLast Reviewed:9/25/2003
Keywords:kbprb KB310421
©2003 Microsoft Corporation. All rights reserved.