logo标签列表

将整数转换为十六进制，以及将十六进制转换为整数。

sqlsql-serverintegerhex
80

我已经在Sybase中使这个查询（其中signal_data是一列）工作了，但在Microsoft SQL Server中它不起作用：

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

我也有它的Excel版本（其中A1包含此值）：

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

有人知道我该如何在SQL Server中完成这个操作吗？

16个回答
2

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

因此，一个像1.5亿这样的大数字只需要6个字符（150,000,000 =“MQGJMU”）。

您也可以使用不同的字符序列作为加密设备。或者传入代码字符和字符长度，并用作加密的盐方法。

然后是反向操作：

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
1
以下是两个函数：dbo.HexToInt 和 dbo.IntToHex，我使用它们进行转换：
if OBJECT_ID('dbo.HexToInt') is not null
    drop function dbo.HexToInt
GO
create function dbo.HexToInt (@chars varchar(max))
returns int
begin
    declare @char varchar(1), @len int, @i int, @r int, @tmp int, @pow int
    set @chars = RTRIM(LTRIM(@chars))
    set @len = LEN(@chars)
    set @i = 1
    set @r = 0
    while @i <= @len
    begin
        set @pow = @len - @i
        set @char = SUBSTRING(@chars, @i, 1)
        if @char = '0'
            set @tmp = 0
        else if @char = '1'
            set @tmp = 1
        else if @char = '2'
            set @tmp = 2
        else if @char = '3'
            set @tmp = 3
        else if @char = '4'
            set @tmp = 4
        else if @char = '5'
            set @tmp = 5
        else if @char = '6'
            set @tmp = 6
        else if @char = '7'
            set @tmp = 7
        else if @char = '8'
            set @tmp = 8
        else if @char = '9'
            set @tmp = 9
        else if @char = 'A'
            set @tmp = 10
        else if @char = 'B'
            set @tmp = 11
        else if @char = 'C'
            set @tmp = 12
        else if @char = 'D'
            set @tmp = 13
        else if @char = 'E'
            set @tmp = 14
        else if @char = 'F'
            set @tmp = 15
        set @r = @r + @tmp * POWER(16,@pow)
        set @i = @i + 1     
    end
    return @r
end

第二个是什么？
if OBJECT_ID('dbo.IntToHex') is not null
    drop function dbo.IntToHex
GO
create function dbo.IntToHex (@val int)
returns varchar(max)
begin
    declare @r varchar(max), @tmp int, @v1 int, @v2 int, @char varchar(1)
    set @tmp = @val
    set @r = ''
    while 1=1
    begin
        set @v1 = @tmp / 16
        set @v2 = @tmp % 16
        if @v2 = 0
            set @char = '0'
        else if @v2 = 1
            set @char = '1'
        else if @v2 = 2
            set @char = '2'
        else if @v2 = 3
            set @char = '3'
        else if @v2 = 4
            set @char = '4'
        else if @v2 = 5
            set @char = '5'
        else if @v2 = 6
            set @char = '6'
        else if @v2 = 7
            set @char = '7'
        else if @v2 = 8
            set @char = '8'
        else if @v2 = 9
            set @char = '9'
        else if @v2 = 10
            set @char = 'A'
        else if @v2 = 11
            set @char = 'B'
        else if @v2 = 12
            set @char = 'C'
        else if @v2 = 13
            set @char = 'D'
        else if @v2 = 14
            set @char = 'E'
        else if @v2 = 15
            set @char = 'F'
        set @tmp = @v1 
        set @r = @char + @r
        if @tmp = 0
            break
    end
    return @r
end
1

给定：

declare @hexStr varchar(16), @intVal int

IntToHexStr：

select @hexStr = convert(varbinary, @intVal, 1)

HexStrToInt：

declare
    @query varchar(100),
    @parameters varchar(50)

select
    @query = 'select @result = convert(int,' + @hb + ')',
    @parameters = '@result int output'

exec master.dbo.Sp_executesql @query, @parameters, @intVal output
0

将十六进制字符串转换为整数，我过去使用过这个方法。实际上，它可以修改为将任何基数转换为整数（八进制、二进制等）。

Declare @Str varchar(200)
Set @str = 'F000BE1A'

Declare @ndx int
Set @ndx = Len(@str)
Declare @RunningTotal  BigInt
Set @RunningTotal = 0

While @ndx > 0
Begin
    Declare @Exponent BigInt
    Set @Exponent = Len(@Str) - @ndx

    Set @RunningTotal = @RunningTotal + 

    Power(16 * 1.0, @Exponent) *
    Case Substring(@str, @ndx, 1)
        When '0' then 0
        When '1' then 1
        When '2' then 2 
        When '3' then 3
        When '4' then 4
        When '5' then 5
        When '6' then 6
        When '7' then 7
        When '8' then 8
        When '9' then 9
        When 'A' then 10
        When 'B' then 11
        When 'C' then 12
        When 'D' then 13
        When 'E' then 14
        When 'F' then 15
    End
    Set @ndx = @ndx - 1
End

Print @RunningTotal
0
IIF(Fields!HIGHLIGHT_COLOUR.Value="","#FFFFFF","#" & hex(Fields!HIGHLIGHT_COLOUR.Value) & StrDup(6-LEN(hex(Fields!HIGHLIGHT_COLOUR.Value)),"0"))

以字体颜色作为表达方式，对我来说是有效的。

-1

从表中选择 to_hex(signal_data) 作为 signal_data

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接