hex to decimal

  • Hi,

    Is there any sql functions that can change hex to decimal and change decimal to hex? e.g. 'FFFF'-> 65535 Thanks a lot.

    Best regards,

    Wallace

  • Not sure if this is what you are looking for?

    select convert(int, 0xFFFF)

    Select convert(varbinary, 65535)

    ---------------------------------------------------------------------------------

  • Yes, I want to change 0xFFFF(hex) <-> 65535(decimal) and vice versa and that's all I want.

    How about any function that can change from character 'FFFF' to hex 0xFFFF and vice versa? Thanks a lot

    Best regards,

    Wallace

  • I dont think there is any in built function for that. (I could be wrong). Lets wait and see if someone has a better suggestion. Thanks.

    ---------------------------------------------------------------------------------

  • We have a third party tool, XLeratorDB, that will do this conversion, and you can install in on the DBMS layer.

    See http://westclintech.com/XLeratorDBengineeringDocumentation/tabid/161/Default.aspx and look through the documentation on Base Conversions.

    Happy New Year.

  • Hi,

    I have two functions that converts between int and hex, perhaps you can use them.

    Create function CRM5.udf_HexToInt(@input varchar(15))

    Returns bigint

    AS

    BEGIN

    Declare @x varchar

    Declare @pos int

    Declare @y bigint

    Declare @z bigint

    Declare @Result bigint

    Set @result = 0

    Set @pos = len(@input)

    Set @y=1

    WHILE @pos >0

    BEGIN

    set @x = lower(substring(@input,@pos,1))

    Set @z = case @x

    when 'a' then 10

    when 'b' then 11

    when 'c' then 12

    when 'd' then 13

    when 'e' then 14

    when 'f' then 15

    else cast(@x as bigint)

    end

    Set @z = @z*@y

    set @result = @result + @z

    Set @pos = @pos-1

    Set @y = @y*16

    END

    Return @result

    END

    And

    Create function crm5.udf_ConvertToHex(@con_id int)

    Returns Varchar(8)

    as

    Begin

    Declare @X int

    Declare @Y int

    Declare @Z int

    Declare @HEX Varchar(8)

    Set @HEX = ''

    Set @X = @con_id

    --print @X

    While @X /16 >=1

    Begin

    --print '@x/16: '+ cast(@X/16 as varchar)

    Set @Y = @X/16

    --print @Y

    --print (@Y*16)

    Set @Z = @X - (@Y*16)

    --print '@Z: ' + cast(@Z as varchar)

    Set @HEX = case @Z

    when 15 then 'f' +@HEX

    when 14 then 'e' +@HEX

    when 13 then 'd' +@HEX

    when 12 then 'c' +@HEX

    when 11 then 'b' +@HEX

    when 10 then 'a' +@HEX

    else cast(@Z as varchar)+@HEX

    end

    Set @X = @X/16

    --print '@X: ' + cast(@X as varchar)

    END

    Set @HEX = cast(@X as varchar)+ @HEX

    while len(@HEX) <8

    Begin

    Set @HEX = '0'+@HEX

    end

    --Print 'HEX: '+ @Hex

    --Print 'DONE'

    Return @HEX

    END

  • klj (1/8/2010)


    Hi,

    I have two functions that converts between int and hex, perhaps you can use them.

    Create function CRM5.udf_HexToInt(@input varchar(15))

    Returns bigint

    AS

    BEGIN

    Declare @x varchar

    Declare @pos int

    Declare @y bigint

    Declare @z bigint

    Declare @Result bigint

    Set @result = 0

    Set @pos = len(@input)

    Set @y=1

    WHILE @pos >0

    BEGIN

    set @x = lower(substring(@input,@pos,1))

    Set @z = case @x

    when 'a' then 10

    when 'b' then 11

    when 'c' then 12

    when 'd' then 13

    when 'e' then 14

    when 'f' then 15

    else cast(@x as bigint)

    end

    Set @z = @z*@y

    set @result = @result + @z

    Set @pos = @pos-1

    Set @y = @y*16

    END

    Return @result

    END

    And

    Create function crm5.udf_ConvertToHex(@con_id int)

    Returns Varchar(8)

    as

    Begin

    Declare @X int

    Declare @Y int

    Declare @Z int

    Declare @HEX Varchar(8)

    Set @HEX = ''

    Set @X = @con_id

    --print @X

    While @X /16 >=1

    Begin

    --print '@x/16: '+ cast(@X/16 as varchar)

    Set @Y = @X/16

    --print @Y

    --print (@Y*16)

    Set @Z = @X - (@Y*16)

    --print '@Z: ' + cast(@Z as varchar)

    Set @HEX = case @Z

    when 15 then 'f' +@HEX

    when 14 then 'e' +@HEX

    when 13 then 'd' +@HEX

    when 12 then 'c' +@HEX

    when 11 then 'b' +@HEX

    when 10 then 'a' +@HEX

    else cast(@Z as varchar)+@HEX

    end

    Set @X = @X/16

    --print '@X: ' + cast(@X as varchar)

    END

    Set @HEX = cast(@X as varchar)+ @HEX

    while len(@HEX) <8

    Begin

    Set @HEX = '0'+@HEX

    end

    --Print 'HEX: '+ @Hex

    --Print 'DONE'

    Return @HEX

    END

    Thank you for your effort and I'd try it 🙂

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply