Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I came across some interesting behaviour on SQL Server (observed in 2005 and 2012) today that I was hoping someone could explain.

A query doing a comparison using = on an NVARCHAR field ignored the trailing space in the string (or auto-trimmed the value before comparison) but the same query using the like operator did not ignore the space. Collation being used is Latin1_General_CI_AS in 2012.

Consider this SQL Fiddle: http://sqlfiddle.com/#!6/72262/4

Note that the like operator does not return a result for the trailing space string, but the = operator does. Why is this?

Bonus points: I am unable to replicate this on a VARCHAR field, I would have thought that a space would be handled in the same way in both data types - is this true?

share|improve this question
    
For fun, also try: SELECT LEN('four ') –  Peter Henell Jan 16 at 7:47
1  
Have a look at the section "Pattern Matching by Using LIKE" in the documentation for LIKE (Transact-SQL). –  Mikael Eriksson Jan 16 at 8:04
add comment

2 Answers 2

up vote 4 down vote accepted

My initial answer suggested that the ANSI_PADDING flag set to OFF may be to blaim for the difference in behavior. However, this is incorrect. The ANSI flag only has an effect on storage, but not equality comparison.

The difference stems from Micosoft's implementation of the SQL standard. The standard says, that when checking for equality, both strings left and right of the equality operator, have to be padded to have the same length. This explains the following results:

insert into test_padding (varchar_clmn, nvarchar_clmn) values ('space ', 'nspace ')
go
-- equality for varchar column
select count(*) from test_padding where varchar_clmn = 'space' -- returns 1
select count(*) from test_padding where varchar_clmn = 'space ' -- returns 1
select count(*) from test_padding where varchar_clmn = 'space    ' --returns 1
-- equality for nvarchar column
select count(*) from test_padding where nvarchar_clmn = 'nspace' -- returns 1
select count(*) from test_padding where nvarchar_clmn = 'nspace ' -- returns 1
select count(*) from test_padding where nvarchar_clmn = 'nspace    ' --returns 1

The LIKE operator does not pad its operands. And also behaves differently for VARCHAR and NVARCHAR column types:

-- likeness for varchar column
select count(*) from test_padding where varchar_clmn like 'space' -- returns 1
select count(*) from test_padding where varchar_clmn like 'space ' -- returns 1
select count(*) from test_padding where varchar_clmn like 'space    ' -- returns 0
-- likeness for nvarchar column
select count(*) from test_padding where nvarchar_clmn like 'nspace' -- returns 0
select count(*) from test_padding where nvarchar_clmn like 'nspace ' -- returns 1
select count(*) from test_padding where nvarchar_clmn like 'nspace    ' -- returns 0

The behavior of the LIKE operator for the unicode type is considered ANSI compliant, that for the ASCII type is SQL Server specific behavior.

Source is here and here.

share|improve this answer
2  
@MartinSmith My first answer was plain wrong, not only on the part you mentioned. See updated answer. –  Ralf Jan 16 at 13:25
    
Great answer, thanks. Did not come across that first source when trying to track this down. –  WT_W Jan 17 at 2:13
add comment

SQL was born in an era when most data processing languages used fixed lengths for every field/variable. Automatic padding of text fields with extra spaces was also part of that picture. To line up with that behaviour, the original SQL CHAR type was explicitly defined for its '=' operator to ignore trailing spaces. (If you find that strange, show me a compelling case where trailing spaces appended to a text have actual real business meaning.)

SQL CHAR types have evolved in all sorts of directions since then, but it is not inconceivable that certain more modern data types have still inherited some characteristics from their historical predecessors.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.