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.
\nFor most people the result is unexpected: padded blanks are ignored!<\/p>\n
Lets see some samples:<\/p>\n
The same applies for unicode:<\/p>\n LIKE behaves a little different. Firstly comparing unicode results different:<\/p>\n And second blanks in the pattern are allways significant:<\/p>\n What about LEN? It behaves consistent to the test for equality: padding blanks are ignored.<\/p>\n So we have some possible solutions, if we neeed to check for equality with blank awareness:<\/p>\n 1. Use allways unicode and LIKE P.S. set @vchar1 = 'Hello '; select – Unicode set @nvchar1 = 'Hello '; select 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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[3],"tags":[],"_links":{"self":[{"href":"http:\/\/www.glorf.it\/blog\/wp-json\/wp\/v2\/posts\/583"}],"collection":[{"href":"http:\/\/www.glorf.it\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.glorf.it\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.glorf.it\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.glorf.it\/blog\/wp-json\/wp\/v2\/comments?post=583"}],"version-history":[{"count":0,"href":"http:\/\/www.glorf.it\/blog\/wp-json\/wp\/v2\/posts\/583\/revisions"}],"wp:attachment":[{"href":"http:\/\/www.glorf.it\/blog\/wp-json\/wp\/v2\/media?parent=583"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.glorf.it\/blog\/wp-json\/wp\/v2\/categories?post=583"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.glorf.it\/blog\/wp-json\/wp\/v2\/tags?post=583"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}'Hello ' = 'Hello' --> true<\/code><\/p>\nN'Hello ' = N'Hello' --> true<\/code><\/p>\n 'Hello ' LIKE 'Hello' --> true
\nN'Hello ' LIKE N'Hello' –> false<\/code><\/p>\n 'Hello' LIKE 'Hello ' --> false
\nN'Hello' LIKE N'Hello ' –> false<\/code><\/p>\nLEN('Hello') --> 5
\nLEN('Hello ') –> 5
\nLEN(N'Hello') –> 5
\nLEN(N'Hello ') –> 5 <\/code><\/p>\nDATALENGTH('Hello') --> 5
\nDATALENGTH('Hello ') –> 6
\nDATALENGTH(N'Hello') –> 10
\nDATALENGTH(N'Hello ') –> 12<\/code><\/p>\n
\n2. add some dummy character at the end:
\n 'Hello'+ '#'= 'Hello '+ '#'
\nN'Hello'+N'#'=N'Hello '+N'#'<\/code>
\n3. convert to varbinary:
\ncast( 'Hello ' as varbinary(10)) = cast( 'Hello' as varbinary(10))
\ncast(N'Hello ' as varbinary(20)) = cast(N'Hello' as varbinary(20))<\/code>
\n4. compare datalength:
\n'Hello'= 'Hello ' AND DATALENGTH( 'Hello')=DATALENGTH( 'Hello ')
\nN'Hello'=N'Hello ' AND DATALENGTH(N'Hello')=DATALENGTH(N'Hello ')<\/code><\/p>\n
\nPlease be aware that the described behavior applies only to varchar and nvarchar, not char\/nchar!
\nCHAR\/NCHAR are always filled with blanks.
\nIf you use ANSI_PADDING OFF trailing blanks are trimmed when inserted in a table.<\/p>\n-- ANSI
\ndeclare @vchar1 varchar(10),
\n\t\t@vchar2 varchar(10);<\/p>\n
\nset @vchar2 = 'Hello';<\/p>\n
\n\t'"'+@vchar1+'"' as "@vchar1",
\n\t'"'+@vchar2+'"' as "@vchar1",
\n\tcase when @vchar1=@vchar2
\n\t\tthen 'true'
\n\t\telse 'false' end as "@vchar1=@vchar2",
\n\tcase when @vchar1 like @vchar2
\n\t\tthen 'true'
\n\t\telse 'false' end as "@vchar1 like @vchar2",
\n\tcase when @vchar2 like @vchar1
\n\t\tthen 'true'
\n\t\telse 'false' end as "@vchar2 like @vchar1", –> false
\n\tcase when @vchar1+'#'=@vchar2+'#'
\n\t\tthen 'true'
\n\t\telse 'false' end as "@vchar1+'#'=@vchar2+'#'", –> false
\n\tcase when cast(@vchar1 as varbinary(10)) = cast(@vchar2 as varbinary(10))
\n\t\tthen 'true'
\n\t\telse 'false' end as "cast(@vchar1 as varbinary(10)) = cast(@vchar2 as varbinary(10))",
\n\tcase when @vchar1=@vchar2 AND DATALENGTH(@vchar1)=DATALENGTH(@vchar2)
\n\t\tthen 'true'
\n\t\telse 'false' end as "@vchar1=@vchar2 AND DATALENGTH(@vchar1)=DATALENGTH(@vchar2)";
\ngo<\/p>\n
\ndeclare @nvchar1 nvarchar(10),
\n\t\t@nvchar2 nvarchar(10);<\/p>\n
\nset @nvchar2 = 'Hello';<\/p>\n
\n\t'"'+@nvchar1+'"' as "@nvchar1",
\n\t'"'+@nvchar2+'"' as "@nvchar1",
\n\tcase when @nvchar1=@nvchar2
\n\t\tthen 'true'
\n\t\telse 'false' end as "@nvchar1=@nvchar2",
\n\tcase when @nvchar1 like @nvchar2
\n\t\tthen 'true'
\n\t\telse 'false' end as "@nvchar1 like @nvchar2",
\n\tcase when @nvchar2 like @nvchar1
\n\t\tthen 'true'
\n\t\telse 'false' end as "@nvchar2 like @nvchar1",
\n\tcase when @nvchar1+'#'=@nvchar2+'#'
\n\t\tthen 'true'
\n\t\telse 'false' end as "@nvchar1+'#'=@nvchar2+'#'",
\n\tcase when cast(@nvchar1 as varbinary(20)) = cast(@nvchar2 as varbinary(20))
\n\t\tthen 'true'
\n\t\telse 'false' end as "cast(@nvchar1 as varbinary(20)) = cast(@nvchar2 as varbinary(20))",
\n\tcase when @nvchar1=@nvchar2 AND DATALENGTH(@nvchar1)=DATALENGTH(@nvchar2)
\n\t\tthen 'true'
\n\t\telse 'false' end as "@nvchar1=@nvchar2 AND DATALENGTH(@nvchar1)=DATALENGTH(@nvchar2)";<\/code><\/p>\n","protected":false},"excerpt":{"rendered":"