SQL Server LEN function reports wrong result

Question

Let's say we have the following casting of an int number into binary value i.e cast(120 as binary(8)) or any other int number into binary(8).

What we normally expect from len(cast(120 as binary(8))) = 8 and this is true unless we try with number 32 where select len(cast(32 as binary(8))) returns 7 !

Is this a bug of SQL Server?

Answer 1

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