convert from hexadecimal to a decimal

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
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
>
>
|||On Wed, 29 Sep 2004 00:27:03 -0400, Steve Kass wrote:

>This is not too efficient, but it should do the trick.
Hi Steve,
How about using a numbers table to speed it up?
create function hexchar2(
@.b varchar(10)
) returns int
as begin
declare @.n bigint
set @.b = substring(@.b,3,8)
set @.n = (select
sum((charindex(left(right(@.b,n),1),'0123456789ABCD EF')-1)*POWER(16,(n-1)))
from dbo.numbers
where n between 1 and len(@.b))
return
case when @.n >= 0X80000000
then @.n - 0x0100000000
else @.n end
end
go
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Very good, and how about this?
create function hexchar2(
@.b varchar(10)
) returns int
as begin
return (
select
sum((charindex(right(left(@.b,N),1),'123456789ABCDE F'))*POWER(16.,len(@.b)-N))
from numbers
where n between 3 and len(@.b))
- case when lower(@.b) like '0x[89abcdef]'+replicate('[0-9abcdef]',7)
then 0x0100000000 else cast(0 as bigint) end
end
SK
Hugo Kornelis wrote:

>On Wed, 29 Sep 2004 00:27:03 -0400, Steve Kass wrote:
>
>
>Hi Steve,
>How about using a numbers table to speed it up?
>create function hexchar2(
> @.b varchar(10)
>) returns int
>as begin
> declare @.n bigint
> set @.b = substring(@.b,3,8)
> set @.n = (select
>sum((charindex(left(right(@.b,n),1),'0123456789ABC DEF')-1)*POWER(16,(n-1)))
> from dbo.numbers
> where n between 1 and len(@.b))
> return
> case when @.n >= 0X80000000
> then @.n - 0x0100000000
> else @.n end
>end
>go
>
>Best, Hugo
>
|||On Fri, 01 Oct 2004 03:35:26 -0400, Steve Kass wrote:

>Very good, and how about this?
>create function hexchar2(
> @.b varchar(10)
>) returns int
>as begin
> return (
> select
>sum((charindex(right(left(@.b,N),1),'123456789ABCD EF'))*POWER(16.,len(@.b)-N))
> from numbers
> where n between 3 and len(@.b))
> - case when lower(@.b) like '0x[89abcdef]'+replicate('[0-9abcdef]',7)
> then 0x0100000000 else cast(0 as bigint) end
>end
>SK
Hi Steve,
Nice!
With the added advantage that it can be used inline in the query, so that
the overhad of calling a function is no longer incurred. (Though I would
comment it if I used it in a query <g>)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||You mean this isn't self-documenting? ;)
select
'0x[89abcdef]' lower, '[0-9abcdef]' upper,
0x100000000 parsename, '123456789ABCDEF' power,
cast(0 as bigint) replicate, 16. stuff,
7 charindex, 3 rtrim
into ltrim
select
b, sum(charindex(right(left(b,N),1),power)
* power(stuff,len(b)-N))
- case when lower(b) like lower+replicate(upper,charindex)
then parsename else replicate end
from T, numbers, ltrim
where n between rtrim and len(b)
group by b, lower, upper, parsename, replicate, power, charindex
go
SK
Hugo Kornelis wrote:

>On Fri, 01 Oct 2004 03:35:26 -0400, Steve Kass wrote:
>
>
>Hi Steve,
>Nice!
>With the added advantage that it can be used inline in the query, so that
>the overhad of calling a function is no longer incurred. (Though I would
>comment it if I used it in a query <g>)
>Best, Hugo
>
|||On Fri, 01 Oct 2004 18:10:12 -0400, Steve Kass wrote:

>You mean this isn't self-documenting? ;)
<snort>
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||hi all,
when i run this query, it will result this :
Server: Msg 207, Level 16, State 3, Procedure hexchar2, Line 5
Invalid column name 'n'.
May i know the 'n' factor is ?

Quote:

Originally posted by Hugo Kornelis
On Wed, 29 Sep 2004 00:27:03 -0400, Steve Kass wrote:

>This is not too efficient, but it should do the trick.
Hi Steve,
How about using a numbers table to speed it up?
create function hexchar2(
@.b varchar(10)
) returns int
as begin
declare @.n bigint
set @.b = substring(@.b,3,8)
set @.n = (select
sum((charindex(left(right(@.b,n),1),'0123456789ABCD EF')-1)*POWER(16,(n-1)))
from dbo.numbers
where n between 1 and len(@.b))
return
case when @.n >= 0X80000000
then @.n - 0x0100000000
else @.n end
end
go
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

|||On Wed, 27 Oct 2004 19:31:12 -0500, landung wrote:

>hi all,
>when i run this query, it will result this :
>Server: Msg 207, Level 16, State 3, Procedure hexchar2, Line 5
>Invalid column name 'n'.
>May i know the 'n' factor is ?
Hi landung,
It's a column in the numbers table I used for this query.
If you don't have a numbers table yet, check out this link:
http://www.aspfaq.com/show.asp?id=2516
If you do have a numbers table, but with another column name, change the
query to reflect the names of your numbers table and column.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hugo,
Don't forget that if you want something that can be put inline without
a UDF call, you can use something more like this:
create function hexchar2(
@.b varchar(10)
) returns int
as begin
return (
select
sum((charindex(right(left(@.b,N),1),'123456789ABCDE F'))*POWER(16.,len(@.b)-N))
from numbers
where n between 3 and len(@.b))
- case when lower(@.b) like '0x[89abcdef]'+replicate('[0-9abcdef]',7)
then 0x0100000000 else cast(0 as bigint) end
end
SK
Hugo Kornelis wrote:

>On Wed, 27 Oct 2004 19:31:12 -0500, landung wrote:
>
>
>Hi landung,
>It's a column in the numbers table I used for this query.
>If you don't have a numbers table yet, check out this link:
>http://www.aspfaq.com/show.asp?id=2516
>If you do have a numbers table, but with another column name, change the
>query to reflect the names of your numbers table and column.
>Best, Hugo
>