Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. INF: How SQL Server Compares Strings with Trailing Spaces

INF: How SQL Server Compares Strings with Trailing Spaces

Summary

SQL Server follows the ANSI/ISO SQL-92 specification (Section 8.2, <Comparison Predicate>, General rules #3) on how to compare strings with spaces. The ANSI standard requires padding for the character strings used in comparisons so that their lengths match before comparing them. The padding directly affects the semantics of WHERE and HAVING clause predicates and other Transact-SQL string comparisons. For example, Transact-SQL considers the strings 'abc' and 'abc ' to be equivalent for most comparison operations.

The only exception to this rule is the LIKE predicate. When the right side of a LIKE predicate expression features a value with a trailing space, SQL Server does not pad the two values to the same length before the comparison occurs. Because the purpose of the LIKE predicate, by definition, is to facilitate pattern searches rather than simple string equality tests, this does not violate the section of the ANSI SQL-92 specification mentioned earlier.

↑ Back to the top

More Information

The SET ANSI_PADDING setting does not affect whether SQL Server pads strings before it compares them. SET ANSI_PADDING only affects whether trailing blanks are trimmed from values being inserted into a table, so it affects storage but not comparisons.

The following query helps to further illustrate the concepts explained in the "Summary" section of this article:


CREATE TABLE #tmp (c1 varchar(10))
GO
INSERT INTO #tmp VALUES ('abc ')
INSERT INTO #tmp VALUES ('abc')
GO
SELECT DATALENGTH(c1) as 'EqualWithSpace', * FROM #tmp WHERE c1 = 'abc '
SELECT DATALENGTH(c1) as 'EqualNoSpace  ', * FROM #tmp WHERE c1 = 'abc'
SELECT DATALENGTH(c1) as 'GTWithSpace   ', * FROM #tmp WHERE c1 > 'ab '
SELECT DATALENGTH(c1) as 'GTNoSpace     ', * FROM #tmp WHERE c1 > 'ab'
SELECT DATALENGTH(c1) as 'LTWithSpace   ', * FROM #tmp WHERE c1 < 'abd '
SELECT DATALENGTH(c1) as 'LTNoSpace     ', * FROM #tmp WHERE c1 < 'abd'
SELECT DATALENGTH(c1) as 'LikeWithSpace ', * FROM #tmp WHERE c1 LIKE 'abc %'
SELECT DATALENGTH(c1) as 'LikeNoSpace   ', * FROM #tmp WHERE c1 LIKE 'abc%'
GO
DROP TABLE #tmp

REFERENCES

For additional information about the ANSI_PADDING setting, click the article numbers below to view the articles in the Microsoft Knowledge Base:

154886 INF: Behavior of ANSI_PADDING
231830 INF: Insertion of Strings Containing Trailing Spaces
SQL Server Books Online

Delaney, Kalen. Inside Microsoft SQL Server 2000. Microsoft Press, 2000. Pages 366-370.

↑ Back to the top

Keywords: kb, kbinfo, kbbillprodsweep

↑ Back to the top

Article Info
Article ID : 316626
Revision : 5
Created on : 8/19/2020
Published on : 8/20/2020
Exists online : False
Views : 116