Not a bug, it's how LEN works. LEN:
Returns the number of characters of the specified string expression,
excluding trailing spaces.
The definition of "trailing space" seems to differ based the datatype. For binary values, a trailing space is when the binary representation "20". In the BOL entry for LEN there is a note that reads,
Use the LEN to return the number of characters encoded into a given
string expression, and DATALENGTH to return the size in bytes for a
given string expression. These outputs may differ depending on the
data type and type of encoding used in the [value]. For more
information on storage differences between different encoding types,
see Collation and Unicode Support.
With Binary the length (LEN
) is reduced by 1 for binary values that end with 20, by 2 for values that end with 2020, etc. Again, it's treating that value like a trailing space. DATALENGTH
resolves this. Note this SQL:
DECLARE
@string VARCHAR(100) = '1234567 ',
@binary BINARY(8) = 32;
SELECT [Type] = 'string', [Len] = LEN(@string), [Datalength] = DATALENGTH(@string)
UNION ALL
SELECT [Type] = 'binary(8)', [Len] = LEN(@binary), [Datalength] = DATALENGTH(@binary);
Returns:
Type Len Datalength
--------- ----------- -----------
string 7 8
binary(8) 7 8
Using my rangeAB function (here) I created this query:
SELECT
N = r.RN,
Binaryvalue = CAST(r.RN AS binary(8)),
[Len] = LEN(CAST(r.RN AS binary(8))),
[DataLength] = DATALENGTH(CAST(r.RN AS binary(8)))
FROM dbo.rangeAB(0,10000,1,0) AS r
WHERE LEN(CAST(r.RN AS binary(8))) <> 8
ORDER BY N;
Note these results:
N Binaryvalue Len DataLength
-------------------- ------------------ ----------- -----------
32 0x0000000000000020 7 8
288 0x0000000000000120 7 8
544 0x0000000000000220 7 8
800 0x0000000000000320 7 8
1056 0x0000000000000420 7 8
1312 0x0000000000000520 7 8
1568 0x0000000000000620 7 8
1824 0x0000000000000720 7 8
2080 0x0000000000000820 7 8
2336 0x0000000000000920 7 8
2592 0x0000000000000A20 7 8
2848 0x0000000000000B20 7 8
3104 0x0000000000000C20 7 8
3360 0x0000000000000D20 7 8
3616 0x0000000000000E20 7 8
3872 0x0000000000000F20 7 8
4128 0x0000000000001020 7 8
4384 0x0000000000001120 7 8
4640 0x0000000000001220 7 8
4896 0x0000000000001320 7 8
5152 0x0000000000001420 7 8
5408 0x0000000000001520 7 8
5664 0x0000000000001620 7 8
5920 0x0000000000001720 7 8
6176 0x0000000000001820 7 8
6432 0x0000000000001920 7 8
6688 0x0000000000001A20 7 8
6944 0x0000000000001B20 7 8
7200 0x0000000000001C20 7 8
7456 0x0000000000001D20 7 8
7712 0x0000000000001E20 7 8
7968 0x0000000000001F20 7 8
8224 0x0000000000002020 6 8
8480 0x0000000000002120 7 8
8736 0x0000000000002220 7 8
8992 0x0000000000002320 7 8
9248 0x0000000000002420 7 8
9504 0x0000000000002520 7 8
9760 0x0000000000002620 7 8
Note how the LEN
of CAST(8224 AS binary(8)
is 6; because 8224 ends with 2020 which is treated like two spaces:
8224 0x0000000000002020 6 8