To go back to Nige's original request for an explanation......
This was an attempt by the SQL Server team to keep the behaviour of tests for string equality, and string comparisons, consistent. The people who then implemented unicode then muddied the water horribly, as you'll see in these tests. Before unicode, the '=', LEN and like all reated trailing spaces in a string as if they didn't exist, simply because that was the way that they treated the older CHAR, where all strings were padded with blanks. The introduction if unicode has meant that LIKE works in a different way with unicode strings, leaving the behavious of LEN inconsistent. Still awake? Look at this and run it. You'll see the issues
DECLARE @testData TABLE (MyID INT IDENTITY(1,1), TestString VARCHAR(100))
INSERT INTO @testdata (TestString)
VALUES ('This is a string ')
INSERT INTO @testdata (TestString)
VALUES ('This is a string ')
INSERT INTO @testdata (TestString)
VALUES ('This is a string ')
INSERT INTO @testdata (TestString)
VALUES ('This is a string')
PRINT 'check the way the string equality operator works'
SELECT * FROM @TestData WHERE teststring = 'This is a string'
PRINT 'check the way the ''Like'' operator works'
SELECT * FROM @TestData WHERE teststring LIKE 'This is a string'
PRINT 'check the way the ''Like'' operator works with unicode SQL-92 standard'
SELECT * FROM @TestData
WHERE CONVERT(NVARCHAR(100),teststring) LIKE 'This is a string'
PRINT 'check the way the ''Like'' operator works with wildcards'
SELECT * FROM @TestData WHERE teststring LIKE '%This is a string %'
PRINT 'does the equality operator work consistently?'
SELECT CASE WHEN teststring = 'This is a string'
THEN ' matched' ELSE ' didn''t match' END
FROM @TestData
PRINT 'Show the original problem with LEN'
SELECT LEN(TestString) FROM @TestData
PRINT 'Show the original problem with LEN, using unicode'
SELECT LEN(CONVERT(NVARCHAR(100),teststring)) FROM @TestData
To quote from BOL....
'LIKE supports ASCII pattern matching and Unicode pattern matching. When all arguments (match_expression, pattern, and escape_character, if present) are ASCII character data types, ASCII pattern matching is performed. If any of the arguments are of Unicode data type, all arguments are converted to Unicode and Unicode pattern matching is performed. When you use Unicode data (nchar or nvarchar data types) with LIKE, trailing blanks are significant; however, for non-Unicode data, trailing blanks are not significant. Unicode LIKE is compatible with the SQL-92 standard. ASCII LIKE is compatible with earlier versions of SQL Server.'
And the work-round for LEN?
PRINT 'and the fix if you want the real length of the string'
SELECT LEN(REPLACE(TestString,' ','|')) FROM @TestData