問題描述
我從表(源)中執行 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).與此查詢無關，只是為了完整性.
Finally, note that binary literals can be specified without conversion simply by prefixing them with "0x" and not using quotes: SELECT 0x0003f80075177fe6 will return a column of type BINARY(8). Not relevant for this query, but just for completeness.
這篇關于將存儲為 VARCHAR 的 BINARY 轉換為 BINARY的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！