将存储为 VARCHAR 的 BINARY 转换为 BINARY

问题描述

我从表(源)中执行 INSERT SELECT，其中每一列都是 VARCHAR 数据类型.

I do an INSERT SELECT from a table (source) where every column is of VARCHAR datatype.

其中一列存储二进制数据，如

One of the columns stores binary data like

'0003f80075177fe6'

我插入的目标表具有相同的列，但具有正确的 BINARY(16) 数据类型.

The destination table, where I insert this, has the same column, but with proper data type of BINARY(16).

INSERT INTO destination
(
    column1,        --type of BINARY(16)
    ...
)
SELECT
    CONVERT(BINARY(16),[varchar_column_storing_binary_data]),   --'0003f80075177fe6'
FROM source
GO

当我插入它，然后选择目标表时，我从 BINARY16 列中得到了一个不同的值:

When I insert it, then select the destination table, I got a different value from the BINARY16 column:

0x30303033663830303735313737666536

这看起来不像是同一个值.

It does not really seems like the same value.

将存储为 VARCHAR 的二进制数据转换为 BINARY 列的正确方法是什么?

What should be the proper way to convert binary data stored as VARCHAR to BINARY column?

推荐答案

你得到的结果是因为字符串0003f80075177fe6"(一个 VARCHAR 值)被转换为代码点，而这些代码点作为二进制值提供.由于您可能正在使用与 ASCII 兼容的排序规则，这意味着您将获得 ASCII 代码点:0 是 48(30 进制)，f 是 102(66 进制)等等.这解释了 30 30 30 33 66 38 30 30...

The result you get is because the string "0003f80075177fe6" (a VARCHAR value) is converted to code points, and these code points are served up as a binary value. Since you're probably using an ASCII-compatible collation, that means you get the ASCII code points: 0 is 48 (30 hex), f is 102 (66 hex) and so on. This explains the 30 30 30 33 66 38 30 30...

您想要做的是将字符串解析为字节的十六进制表示 (00 03 f8 00 75 71 77 fe 66).CONVERT 接受额外的样式"允许您转换十六进制字符串的参数:

What you want to do instead is parse the string as a hexadecimal representation of the bytes (00 03 f8 00 75 71 77 fe 66). CONVERT accepts an extra "style" parameter that allows you to convert hexstrings:

SELECT CONVERT(BINARY(16), '0003f80075177fe6', 2)

样式 2 将十六进制字符串转换为二进制字符串.(样式 1 对以0x"开头的字符串执行相同的操作，但此处并非如此.)

Style 2 converts a hexstring to binary. (Style 1 does the same for strings that start with "0x", which is not the case here.)

请注意，如果少于 16 个字节(如本例中所示)，则该值右填充零(0x0003F80075177FE60000000000000000).如果您需要左填充，则必须自己执行此操作:

Note that if there are less than 16 bytes (as in this case), the value is right-padded with zeroes (0x0003F80075177FE60000000000000000). If you need it left-padded instead, you have to do that yourself:

SELECT CONVERT(BINARY(16), RIGHT(REPLICATE('00', 16) + '0003f80075177fe6', 32), 2)

最后，请注意，二进制文字可以在不进行转换的情况下简单地通过在它们前面加上0x"而不使用引号来指定:SELECT 0x0003f80075177fe6 将返回类型为 BINARY(8).与此查询无关，只是为了完整性.