In SQL Server 2008 I've got a table known as Zone having a column ZoneReference varchar(50) not null because the primary key.

Basically run the next query:

select '"' + ZoneReference + '"' as QuotedZoneReference
from Zone
where ZoneReference = 'WF11XU'

I recieve the next result:

"WF11XU "

Note the trailing space.

How's this possible? When the trailing space is really there on that row, then I'd be prepared to return zero results, so I am presuming it is something else that SQL Server Management Studio is exhibiting strangely.

In C# code calling zoneReference.Trim() removes it, recommending it's some kind of whitespace character.

Can anybody help?

This is the expected result: in SQL Server the = operator ignores trailing spaces when creating the comparison.

SQL Server follows the ANSI/ISO SQL-92 specs (Section 8.2, , General rules #3) regarding how to compare strings with spaces. The ANSI standard requires padding for that character strings utilized in evaluations to ensure that their measures match before evaluating them. The padding directly affects the semantics of WHERE and Getting clause predicates along with other Transact-SQL string evaluations. For instance, Transact-SQL views the strings 'abc' and 'abc ' to become equivalent for many comparison procedures.

The only real exception for this rule may be the LIKE predicate. Once the right side of the LIKE predicate expression includes a value having a trailing space, SQL Server doesn't pad the 2 values towards the same length prior to the comparison happens. Because the objective of so on predicate, obviously, would be to facilitate pattern searches instead of simple string equality tests, this doesn't violate the portion of the ANSI SQL-92 specs pointed out earlier.

Source

Yeah, Mark is correct. Run the next SQL:

create table #temp (name varchar(15))
insert into #temp values ('james ')
select '"' + name + '"' from #temp where name ='james'
select '"' + name + '"' from #temp where name like 'james'
drop table #temp

But, the assertion concerning the 'like' statement seems to not operate in the above mentioned example. Output:

(1 row(s) affected)

-----------------
"james "

(1 row(s) affected)


-----------------
"james "

(1 row(s) affected)

EDIT: To have it to operate, you can put in the finish:

and name <> rtrim(ltrim(name))

Ugly though.

EDIT2: Because of the comments abovem, the next works:

select '"' + name + '"' from #temp where 'james' like name

try

    select Replace('"' + ZoneReference + '"'," ", "") as QuotedZoneReference from Zone where ZoneReference = 'WF11XU'