SQL WHERE clause matching values with trailing spaces

Question

In SQL Server 2008 I have a table called Zone with a column ZoneReference varchar(50) not null as the primary key.

If I run the following query:

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

I get the following result:

"WF11XU "

Note the trailing space.

How is this possible? If the trailing space really is there on that row, then I'd expect to return zero results, so I'm assuming it's something else that SQL Server Management Studio is displaying weirdly.

In C# code calling zoneReference.Trim() removes it, suggesting it is some sort of whitespace character.

Can anyone help?

Solution 1

That's the expected result: in SQL Server the = operator ignores trailing spaces when making the comparison.

SQL Server follows the ANSI/ISO SQL-92 specification (Section 8.2, , 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.

Source

Solution 2

Trailing spaces are not always ignored. I experienced this issue today. My table had NCHAR columns and was being joined to VARCHAR data. Because the data in the table was not as wide as its field, trailing spaces were automatically added by SQL Server.

I had an ITVF (inline table-valued function) that took varchar parameters. The parameters were used in a JOIN to the table with the NCHAR fields.

The joins failed because the data passed to the function did not have trailing spaces but the data in the table did. Why was that?

I was getting tripped up on DATA TYPE PRECEDENCE. (See http://technet.microsoft.com/en-us/library/ms190309.aspx)

When comparing strings of different types, the lower precedence type is converted to the higher precedence type before the comparison. So my VARCHAR parameters were converted to NCHARs. The NCHARs were compared, and apparently the spaces were significant.

How did I fix this? I changed the function definition to use NVARCHAR parameters, which are of a higher precedence than NCHAR. Now the NCHARs were changed automatically by SQL Server into NVARCHARs and the trailing spaces were ignored.

Why didn't I just perform an RTRIM? Testing revealed that RTRIM killed the performance, preventing the JOIN optimizations that SQL Server would have otherwise used.

Why not change the data type of the table? The tables are already installed on customer sites, and they do not want to run maintenance scripts (time + money to pay DBAs) or give us access to their machinines (understandable).

Solution 3

Yeah, Mark is correct. Run the following 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 about the 'like' statement appears not to work in the above example. Output:

(1 row(s) affected)

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

(1 row(s) affected)


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

(1 row(s) affected)

EDIT: To get it to work, you could put at the end:

and name <> rtrim(ltrim(name))

Ugly though.

EDIT2: Given the comments abovem, the following would work:

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

Comments

[Neil Barnwell]

You've missed the point slightly. The point was that I didn't expect where clause to match this record.

[James Wiseman]

Yeah, I think Neil knew how to do it, he just wondered why he was seeing the observed behaviour.

[a1ex07]

I believe WHERE ZoneReference = 'WF11XU' AND DATALENGTH(ZoneReference) = DATALENGTH('WF11XU') will also work and might be faster than LIKE

[Neil Barnwell]

@MarkByers Yes I see - I tried where 'WF11XU' like ZoneReference and that worked! Weirder and weirder. Still, every day's a school day!

[Neil Barnwell]

I downvoted I'm afraid because this post was unhelpful, in that it detracted from the actual question being answered.

[McX]

LIKE doesn't work both ways, SELECT IIF('WF11XU ' LIKE 'WF11XU', 1, 0) returns 1