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

Article translations Article translations
Article ID: 310421 - View products that this article applies to.
This article was previously published under Q310421
Expand all | Collapse all

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'
				

Properties

Article ID: 310421 - Last Review: September 25, 2003 - Revision: 3.1
APPLIES TO
  • Microsoft SQL Server 2000 Standard Edition
Keywords: 
kbprb KB310421

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com