SQL 将整数转换为十六进制和将十六进制转换为整数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/703019/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Convert integer to hex and hex to integer
提问by Nick Sinas
So I have this query working (where signal_datais a column) in Sybase but it doesn't work in Microsoft SQL Server:
所以我有这个查询signal_data在 Sybase 中工作（列在哪里），但它在 Microsoft SQL Server 中不起作用：
HEXTOINT(SUBSTRING((INTTOHEX(signal_data)),5,2)) as Signal
I also have it in Excel (where A1contains the value):
我在 Excel 中也有它（其中A1包含值）：
=HEX2DEC(LEFT(DEC2HEX(A1),LEN(DEC2HEX(A1))-2))
Does anyone know how I would do this in SQL Server?
有谁知道我将如何在 SQL Server 中做到这一点？
回答by Bill Karwin
Convert INT to hex:
将 INT 转换为十六进制：
SELECT CONVERT(VARBINARY(8), 16777215)
Convert hex to INT:
将十六进制转换为 INT：
SELECT CONVERT(INT, 0xFFFFFF)
Update 2015-03-16
更新 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:
上面的示例有一个限制，即它仅在 HEX 值作为整数文字给出时才有效。为完整起见，如果要转换的值是十六进制字符串（例如在 varchar 列中找到），请使用：
-- 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.
注意：字符串必须包含偶数个十六进制数字。奇数位数将产生错误。
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.
可以在CAST 和 CONVERT (Transact-SQL)的“二进制样式”部分找到更多详细信息。我相信需要 SQL Server 2008 或更高版本。
回答by justinpitts
Actually, the built-in function is named master.dbo.fn_varbintohexstr.
实际上，内置函数名为 master.dbo.fn_varbintohexstr。
So, for example:
因此，例如：
SELECT 100, master.dbo.fn_varbintohexstr(100)
Gives you
给你
100 0x00000064
100 0x00000064
回答by Kip Bryan
SQL Server equivalents to Excel's string-based DEC2HEX, HEX2DEC functions:
SQL Server 相当于 Excel 基于字符串的 DEC2HEX、HEX2DEC 函数：
--Convert INT to hex string:
PRINT CONVERT(VARCHAR(8),CONVERT(VARBINARY(4), 16777215),2) --DEC2HEX
--Convert hex string to INT:
PRINT CONVERT(INT,CONVERT(VARBINARY(4),'00FFFFFF',2)) --HEX2DEC
回答by DenNukem
Convert int to hex:
将整数转换为十六进制：
SELECT FORMAT(512+255,'X')
SELECT FORMAT(512+255,'X')
回答by wndproc
It is possible using the function FORMAT available on SQL Server 2012 and above
可以使用 SQL Server 2012 及更高版本上可用的函数 FORMAT
select FORMAT(10,'x2')
Results in:
结果是：
0a
回答by Neel Edwards
The traditonal 4 bit hex is pretty direct. Hex String to Integer (Assuming value is stored in field called FHexString) :
传统的 4 位十六进制非常直接。十六进制字符串到整数（假设值存储在名为 FHexString 的字段中）：
CONVERT(BIGINT,CONVERT(varbinary(4),
(SELECT master.dbo.fn_cdc_hexstrtobin(
LEFT(FMEID_ESN,8)
))
))
Integer to Hex String (Assuming value is stored in field called FInteger):
整数到十六进制字符串（假设值存储在名为 FInteger 的字段中）：
(SELECT master.dbo.fn_varbintohexstr(CONVERT(varbinary,CONVERT(int,
FInteger
))))
Important to note is that when you begin to use bit sizes that cause register sharing, especially on an intel machine, your High and Low and Left and Rights in the registers will be swapped due to the little endian nature of Intel. For example, when using a varbinary(3), we're talking about a 6 character Hex. In this case, your bits are paired as the following indexes from right to left "54,32,10". In an intel system, you would expect "76,54,32,10". Since you are only using 6 of the 8, you need to remember to do the swaps yourself. "76,54" will qualify as your left and "32,10" will qualify as your right. The comma separates your high and low. Intel swaps the high and lows, then the left and rights. So to do a conversion...sigh, you got to swap them yourselves for example, the following converts the first 6 of an 8 character hex:
需要注意的重要一点是，当您开始使用导致寄存器共享的位大小时，尤其是在英特尔机器上，由于英特尔的小端特性，您在寄存器中的高位和低位以及左位和右位将被交换。例如，当使用 varbinary(3) 时，我们谈论的是 6 个字符的十六进制。在这种情况下，您的位从右到左按以下索引“54,32,10”配对。在英特尔系统中，您会期望“76、54、32、10”。由于您只使用了 8 个中的 6 个，因此您需要记住自己进行交换。“76,54” 将成为您的左侧，“32,10”将成为您的右侧。逗号分隔你的高低。英特尔交换高点和低点，然后是左右。所以要进行转换...叹息，例如，您必须自己交换它们，
(SELECT master.dbo.fn_replvarbintoint(
CONVERT(varbinary(3),(SELECT master.dbo.fn_cdc_hexstrtobin(
--intel processors, registers are switched, so reverse them
----second half
RIGHT(FHex8,2)+ --0,1 (0 indexed)
LEFT(RIGHT(FHex8,4),2)+ -- 2,3 (oindex)
--first half
LEFT(RIGHT(FHex8,6),2) --4,5
)))
))
It's a bit complicated, so I would try to keep my conversions to 8 character hex's (varbinary(4)).
这有点复杂，所以我会尽量保持我的转换为 8 个字符的十六进制 (varbinary(4))。
In summary, this should answer your question. Comprehensively.
总之，这应该可以回答您的问题。综合。
回答by Maksym Kozlenko
Here is the function for SQL server which converts integer value into its hexadecimal representation as a varchar. It should be easy to adapt to other database types
这是 SQL 服务器的函数，它将整数值转换为 varchar 的十六进制表示。应该很容易适应其他数据库类型
For example:
例如：
SELECT dbo.ToHex(4095) --> FFF
SQL:
查询语句：
CREATE FUNCTION ToHex(@value int)
RETURNS varchar(50)
AS
BEGIN
DECLARE @seq char(16)
DECLARE @result varchar(50)
DECLARE @digit char(1)
SET @seq = '0123456789ABCDEF'
SET @result = SUBSTRING(@seq, (@value%16)+1, 1)
WHILE @value > 0
BEGIN
SET @digit = SUBSTRING(@seq, ((@value/16)%16)+1, 1)
SET @value = @value/16
IF @value <> 0 SET @result = @digit + @result
END
RETURN @result
END
GO
回答by Maksym Kozlenko
Use master.dbo.fnbintohexstr(16777215)to convert to a varcharrepresentation.
使用master.dbo.fnbintohexstr(16777215)转换为varchar表示。
回答by Donaciano
Declare @Dato xml
Set @Dato = Convert(xml, '<dato>FF</dato>')
Select Cast( rw.value( 'xs:hexBinary( text()[1])' , 'varbinary(max)' ) as int ) From @Dato.nodes('dato') as T(rw)
回答by Jason Gilley
The answer by Maksym Kozlenko is nice and can be slightly modified to handle encoding a numeric value to any code format. For example:
Maksym Kozlenko 的答案很好，可以稍微修改以处理将数值编码为任何代码格式。例如：
CREATE FUNCTION [dbo].[IntToAlpha](@Value int)
RETURNS varchar(30)
AS
BEGIN
DECLARE @CodeChars varchar(100)
SET @CodeChars = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
DECLARE @CodeLength int = 26
DECLARE @Result varchar(30) = ''
DECLARE @Digit char(1)
SET @Result = SUBSTRING(@CodeChars, (@Value % @CodeLength) + 1, 1)
WHILE @Value > 0
BEGIN
SET @Digit = SUBSTRING(@CodeChars, ((@Value / @CodeLength) % @CodeLength) + 1, 1)
SET @Value = @Value / @CodeLength
IF @Value <> 0 SET @Result = @Digit + @Result
END
RETURN @Result
END
So, a big number like 150 million, becomes only 6 characters (150,000,000 = "MQGJMU")
所以，像 1.5 亿这样的大数字，变成只有 6 个字符（150,000,000 = "MQGJMU"）
You could also use different characters in different sequences as an encrypting device. Or pass in the code characters and length of characters and use as a salting method for encrypting.
您还可以使用不同序列中的不同字符作为加密设备。或者传入编码字符和字符长度作为加盐方法进行加密。
And the reverse:
反过来：
CREATE FUNCTION [dbo].[AlphaToInt](@Value varchar(7))
RETURNS int
AS
BEGIN
DECLARE @CodeChars varchar(100)
SET @CodeChars = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
DECLARE @CodeLength int = 26
DECLARE @Digit char(1)
DECLARE @Result int = 0
DECLARE @DigitValue int
DECLARE @Index int = 0
DECLARE @Reverse varchar(7)
SET @Reverse = REVERSE(@Value)
WHILE @Index < LEN(@Value)
BEGIN
SET @Digit = SUBSTRING(@Reverse, @Index + 1, 1)
SET @DigitValue = (CHARINDEX(@Digit, @CodeChars) - 1) * POWER(@CodeLength, @Index)
SET @Result = @Result + @DigitValue
SET @Index = @Index + 1
END
RETURN @Result
