tsql - SQL Server LEN 函数报告错误结果

Question

假设我们有以下将 int 数字转换为二进制值的方法，即cast(120 as binary(8)) 或任何其他整数到 binary(8)。

我们通常对 len(cast(120 as binary(8))) = 8 的期望是正确的，除非我们尝试使用数字 32，其中 select len(cast(32 as binary(8))) 返回 7 !

这是SQL Server的bug吗？

Answer 1

不是错误，它是 LEN 的工作方式。莱恩:

Returns the number of characters of the specified string expression, excluding trailing spaces.

“尾随空格”的定义似乎因数据类型而异。对于二进制值，尾随空格是二进制表示“20”。在 LEN 的 BOL 条目中，有一个注释，上面写着，

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.

对于 Binary，对于以 20 结尾的二进制值，长度 (LEN) 减少 1，对于以 2020 结尾的值，长度减少 2，等等。同样，它将该值视为尾随空格. DATALENGTH 解决了这个问题。请注意此 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);

返回:

Type      Len         Datalength
--------- ----------- -----------
string    7           8
binary(8) 7           8

使用我的 rangeAB 函数 ( here ) 我创建了这个查询:

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;

注意这些结果:

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

请注意 CAST(8224 AS binary(8) 的 LEN 是 6；因为 8224 以 2020 结尾，这被视为两个空格:

8224                 0x0000000000002020 6           8