将整数转换为十六进制和十六进制转换为整数

2022-01-14 00:00:00 sql integer hex sql-server

所以我在 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.

相关文章