Automatic RTRIM?

Could anyone explain why this happens:

-- All outputs works but only the first should

select 'works' where '1' = '1'

select 'works' where '1' = '1 '

select 'works' where '1 ' = '1'

Seems to me like trailing blanks are automatically trimmed, why?

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. (Microsoft, 2003).

Check this out at http://support.microsoft.com/default.aspx?scid=kb;en-us;316626

You can dig up lots of interesting ones, such as:

select 'works' where char(32) = ''

select 'works' where '1' + char (32) = '1'

In BOL, read about some of the string functions, such as Len:

LEN

Returns the number of characters, rather than the number of bytes, of the given string expression, excluding trailing blanks.

Hope this helps.

Lee Everest

MCP MCDBA

Adjunct SQL Instructor

North Lake College
www.texastoo.com/sqlblog

|||An interesting follow-up:

'1' = '1 ' is true
'1' like ' 1 ' is false

LIKE treats the trailing spaces as significant while = does not.
|||

Is there supposed to be a second "1" in your "like" example?

Currently it reads: '1' like ' ' is false

I'm thinking you meant: '1' like '1 ' is false

Or maybe I'm missing something?

Dan

|||You are correct. Typo fixed.
|||;-)