将整数转换为十六进制和十六进制转换为整数 [英] Convert integer to hex and hex to integer

问题描述

所以我在 Sybase 中有这个查询(其中 signal_data 是一列)，但它在 Microsoft SQL Server 中不起作用:

So I have this query working (where signal_data is a column) in Sybase but it doesn't work in Microsoft SQL Server:

HEXTOINT(SUBSTRING((INTTOHEX(signal_data)),5,2)) as Signal

我在 Excel 中也有它(其中 A1 包含值):

I also have it in Excel (where A1 contains the value):

=HEX2DEC(LEFT(DEC2HEX(A1),LEN(DEC2HEX(A1))-2))

有人知道我将如何在 SQL Server 中执行此操作吗?

Does anyone know how I would do this in SQL Server?

推荐答案

将INT转换为十六进制:

Convert INT to hex:

SELECT CONVERT(VARBINARY(8), 16777215)

将十六进制转换为 INT:

Convert hex to INT:

SELECT CONVERT(INT, 0xFFFFFF)

2015-03-16 更新

上面的例子有一个限制，它只在十六进制值作为整数文字给出时才有效.为了完整起见，如果要转换的值是十六进制字符串(例如在 varchar 列中找到)，请使用:

Update 2015-03-16

The above example has the limitation that it only works when the HEX value is given as an integer literal. For completeness, if the value to convert is a hexadecimal string (such as found in a varchar column) use:

-- If the '0x' marker is present:
SELECT CONVERT(INT, CONVERT(VARBINARY, '0x1FFFFF', 1))

-- If the '0x' marker is NOT present:
SELECT CONVERT(INT, CONVERT(VARBINARY, '1FFFFF', 2))

注意:字符串必须包含偶数个十六进制数字.奇数位数会产生错误.

Note: The string must contain an even number of hex digits. An odd number of digits will yield an error.

更多详细信息可以在 CAST and CONVERT 的二进制样式"部分中找到(Transact-SQL).我认为需要 SQL Server 2008 或更高版本.

More details can be found in the "Binary Styles" section of CAST and CONVERT (Transact-SQL). I believe SQL Server 2008 or later is required.

这篇关于将整数转换为十六进制和十六进制转换为整数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！