Re: convert from hexadecimal to a decimal

From: Steve Kass (skass_at_drew.edu)
Date: 09/29/04 

Date: Wed, 29 Sep 2004 00:27:03 -0400

Skip,

  This is not too efficient, but it should do the trick. It doesn't
validate the input at all, either.

create function hexchar(
  @b varchar(10)
) returns int
as begin
  declare @n bigint
  set @n = 0
  declare @digits char(16)
  set @digits = '0123456789ABCDEF'
  set @b = substring(@b,3,8)
  while len(@b) > 0 begin
    set @n = 16*@n + charindex(substring(@b,1,1),@digits)-1
    set @b = substring(@b,2,8)
  end
  return
    case when @n >= 0X80000000
         then @n - 0x0100000000
    else @n end
end
go

-- Steve Kass
-- Drew University
-- Ref: 8EB8CE54-6E8E-47C1-93AB-35AB3F7C27F5

Skip wrote:

>We have a tableA with a varchar field whose contents are
>hexadecimal. We want to insert these hexadecimal contents
>from a varchar field into different table, tableB in
>decimal format.
>
>We tried to use Cast and convert functions to explicitly
>convert into int field thinking it will give us right
>decimal, which didn't work.
>
>
>Example of what works:
>Select CAST(Cast(cast(0x2A as varchar) AS varbinary) AS
>int) returns value of 42.
>
>This doesn't work:
>Select CAST(Cast(cast('0x2A' as varchar) AS varbinary) AS
>int)
>
>All the values coming from tableA are in '0x2A' form
>because the field is defined as a varchar. We can set the
>field name in tableB to whatever we want (int, decimal or
>even varbinary)
>
>Could someone please pointers / suggestions on how to
>convert hexadecimal to int or even how to remove the
>leading and trailing ' ?
>
>Thanks in advance
>
>
>
>

Relevant Pages