SQL Server 中十六进制常量和十进制常量的区别 [英] difference between hex constants and decimal constants in SQL server

问题描述

作为 在来自 SQL 脚本的 nvarchar 或 varchar? :

下面 SQL 脚本中 0x 前缀的十六进制常量和十进制常量有什么区别?

What is the difference between the 0x prefixed hexadecimal constants and the decimal ones in the below SQL script?

看起来以 0x 为前缀的不传递整数.今晚我可能又要度过另一个无聊的时刻了，所以请赐教 (:

It looks like the 0x prefixed ones don't deliver integers. I'm probably in for another duh moment this evening, so please enlighten me (:

select 
  char(0x64) charx64, 
  char(0100) char100, 
  nchar(0x64) ncharx64, 
  nchar(0100) nchar100, 
  char(0x6564) charx6564, 
  char(025956) char25956, 
  nchar(0x6564) ncharx6564, 
  nchar(025956) nchar25956, 
  0x64 x64, 
  0100 d100, 
  0x6564 x6564, 
  025956 d25956, 
  cast(0x64 as varchar) x64varchar, 
  cast(0100 as varchar) d100varchar, 
  cast(0x6564 as varchar) x6564varchar, 
  cast(025956 as varchar) d25956varchar, 
  cast(0x64 as nvarchar) x64nvarchar, 
  cast(0100 as nvarchar) d100nvarchar, 
  cast(0x6564 as nvarchar) x6564nvarchar, 
  cast(025956 as nvarchar) d25956nvarchar, 
  null
;

(null 在那里，所以更容易生成这个语句)

(the null is there so it was easier to generate this statement)

结果:

C:\bin>"C:\Program Files\Microsoft SQL Server\110\Tools\Binn\SQLCMD.EXE" -S .\SQLEXPRESS -E -i hex-versus-decimal.sql 
charx64 char100 ncharx64 nchar100 charx6564 char25956 ncharx6564 nchar25956 x64  d100        x6564  d25956      x64varchar                     d100varchar                    x6564varchar                   d25956varchar                  x64nvarchar                    d100nvarchar                   x6564nvarchar                  d25956nvarchar                            
------- ------- -------- -------- --------- --------- ---------- ---------- ---- ----------- ------ ----------- ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ -----------
d       d       d        d        NULL      NULL      ?          ?          0x64         100 0x6564       25956 d                              100                            ed                             25956                          d                              100                            ?                              25956                                 NULL

(1 rows affected)

推荐答案

对于 MS SQL 服务器，0x 常量是 二进制文字，而不是整数(更多关于 binary和 varbinary 类型).当您对它们调用 char() 和 nvarchar() 时，它们会被转换为整数(如果我没记错的话，就好像它们是大端整数表示一样).当您将它们转换为 varchar/nvarchar 时，它们会被解释为 ANSI 或 UCS2 编码文本的字节.

For MS SQL server, 0x constants are binary literals, not integers (more on binary and varbinary types). When you call char() and nvarchar() on them, they are converted to integers (as if they were big-endian integer representations, if I remember correctly). When you cast them to varchar/nvarchar, they're interpreted as bytes of ANSI- or UCS2-encoded text.

这篇关于SQL Server 中十六进制常量和十进制常量的区别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！