When SQL Server compares two strings, it makes a comparison from left to right and treats the shorter expression as though it was padded with spaces to equal the length of the longer string.
For most people the result is unexpected: padded blanks are ignored!
Lets see some samples:
'Hello ' = 'Hello' --> true
The same applies for unicode:
N'Hello ' = N'Hello' --> true
LIKE behaves a little different. Firstly comparing unicode results different:
'Hello ' LIKE 'Hello' --> true
N'Hello ' LIKE N'Hello' –> false
And second blanks in the pattern are allways significant:
'Hello' LIKE 'Hello ' --> false
N'Hello' LIKE N'Hello ' –> false
What about LEN? It behaves consistent to the test for equality: padding blanks are ignored.
LEN('Hello') --> 5
LEN('Hello ') –> 5
LEN(N'Hello') –> 5
LEN(N'Hello ') –> 5
DATALENGTH('Hello') --> 5
DATALENGTH('Hello ') –> 6
DATALENGTH(N'Hello') –> 10
DATALENGTH(N'Hello ') –> 12
So we have some possible solutions, if we neeed to check for equality with blank awareness:
1. Use allways unicode and LIKE
2. add some dummy character at the end:
'Hello'+ '#'= 'Hello '+ '#'
N'Hello'+N'#'=N'Hello '+N'#'
3. convert to varbinary:
cast( 'Hello ' as varbinary(10)) = cast( 'Hello' as varbinary(10))
cast(N'Hello ' as varbinary(20)) = cast(N'Hello' as varbinary(20))
4. compare datalength:
'Hello'= 'Hello ' AND DATALENGTH( 'Hello')=DATALENGTH( 'Hello ')
N'Hello'=N'Hello ' AND DATALENGTH(N'Hello')=DATALENGTH(N'Hello ')
P.S.
Please be aware that the described behavior applies only to varchar and nvarchar, not char/nchar!
CHAR/NCHAR are always filled with blanks.
If you use ANSI_PADDING OFF trailing blanks are trimmed when inserted in a table.
-- ANSI
declare @vchar1 varchar(10),
@vchar2 varchar(10);
set @vchar1 = 'Hello ';
set @vchar2 = 'Hello';
select
'"'+@vchar1+'"' as "@vchar1",
'"'+@vchar2+'"' as "@vchar1",
case when @vchar1=@vchar2
then 'true'
else 'false' end as "@vchar1=@vchar2",
case when @vchar1 like @vchar2
then 'true'
else 'false' end as "@vchar1 like @vchar2",
case when @vchar2 like @vchar1
then 'true'
else 'false' end as "@vchar2 like @vchar1", –> false
case when @vchar1+'#'=@vchar2+'#'
then 'true'
else 'false' end as "@vchar1+'#'=@vchar2+'#'", –> false
case when cast(@vchar1 as varbinary(10)) = cast(@vchar2 as varbinary(10))
then 'true'
else 'false' end as "cast(@vchar1 as varbinary(10)) = cast(@vchar2 as varbinary(10))",
case when @vchar1=@vchar2 AND DATALENGTH(@vchar1)=DATALENGTH(@vchar2)
then 'true'
else 'false' end as "@vchar1=@vchar2 AND DATALENGTH(@vchar1)=DATALENGTH(@vchar2)";
go
– Unicode
declare @nvchar1 nvarchar(10),
@nvchar2 nvarchar(10);
set @nvchar1 = 'Hello ';
set @nvchar2 = 'Hello';
select
'"'+@nvchar1+'"' as "@nvchar1",
'"'+@nvchar2+'"' as "@nvchar1",
case when @nvchar1=@nvchar2
then 'true'
else 'false' end as "@nvchar1=@nvchar2",
case when @nvchar1 like @nvchar2
then 'true'
else 'false' end as "@nvchar1 like @nvchar2",
case when @nvchar2 like @nvchar1
then 'true'
else 'false' end as "@nvchar2 like @nvchar1",
case when @nvchar1+'#'=@nvchar2+'#'
then 'true'
else 'false' end as "@nvchar1+'#'=@nvchar2+'#'",
case when cast(@nvchar1 as varbinary(20)) = cast(@nvchar2 as varbinary(20))
then 'true'
else 'false' end as "cast(@nvchar1 as varbinary(20)) = cast(@nvchar2 as varbinary(20))",
case when @nvchar1=@nvchar2 AND DATALENGTH(@nvchar1)=DATALENGTH(@nvchar2)
then 'true'
else 'false' end as "@nvchar1=@nvchar2 AND DATALENGTH(@nvchar1)=DATALENGTH(@nvchar2)";
