Is there a way to convert hex to decimal?
-
I'm trying to get the decimal equivalent of a hex. This is what a piece of data looks like: 653D0A.
This is actually a string. I need only the 1st 2 characters (65), which represent the hex, so the plan is to strip them out. But then what?
-
if it is stored as hex then just convert it:
select convert( varchar(3) , 0x653D0A )
But there are probably a half a dozen UDFs in the script archives on this site that will do various conversions.
-
There is the similar post I just found.. may be of help.
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=175193
-
I guess this is what you want?
select dbo.fn_basetodec('653D0A', 16)
--------------------
6634762
(1 row(s) affected)
The function is from SQL Server Magazine June 2005 by Itzik. The neat thing is that it solves the problem where you have hex values stored as charachters. Apart from the function itself, you also need a numbers table.
if object_id('dbo.fn_basetodec') is not null drop function dbo.fn_basetodec
go
create function dbo.fn_basetodec ( @val as varchar(63), @base as int )
returns bigint
as
-- by Itzik, SQL Server Mag June 2005
begin
return
( select sum(
( charindex(
substring(@val, len(@val) - n + 1, 1),
'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ') - 1)
* power(cast(@base as bigint), n - 1))
from nums
where n <= len(@val))
end
go
/* Need numberstable for the function
if object_id('dbo.nums') is not null drop table dbo.nums
go
create table dbo.nums(n int not null)
set nocount on
declare @max-2 as int, @rc as int
set @max-2 = 8000
set @rc = 1
begin tran
insert nums values (1)
begin
insert nums
select n + @rc
from nums
end
insert nums
select n + @rc from nums
commit
go
alter table nums add primary key(n)
set nocount off
go
*/
/Kenneth
-
I use the following UDF for Hex to Dec conversions. In my tests, it is marginally faster than the fn_basetodec, and it doesn't require a numbers table:
CREATE FUNCTION dbo.fnHex2BigInt
(
@hexNum varchar(20)
)
RETURNS bigint
AS
BEGIN
DECLARE @hexdigits varchar(16), @next char(1), @num bigint, @multiplier bigint
DECLARE @cpos int, @cpos_max int
SET @cpos_max = Len(@hexNum)
-------------------------------------------------------------
-- largest bigint is 9223372036854775807, or 7FFFFFFFFFFFFFFF
IF @cpos_max > 16
BEGIN
RETURN NULL
END
IF @cpos_max = 16 AND Left(@hexnum,1) > '7'
BEGIN
RETURN NULL
END
-------------------------------------------------------------
SET @hexdigits = '0123456789ABCDEF'
SET @multiplier = 1
SET @num = 0
SET @cpos = @cpos_max
WHILE @cpos > 0
BEGIN
SET @next = Substring(@hexnum, @cpos, 1)
SET @num = @num + CONVERT(bigint, (CharIndex(@next , @hexdigits, 1) - 1)) * @multiplier
SET @cpos = @cpos - 1
IF @cpos > 0
SET @multiplier = @multiplier * CONVERT(bigint, 16)
END
RETURN @num
END
-
Yes, it is faster, though fn_basetodec has another feature - you enter the base as a parameter (2-36)
So you can use it for different situations, like
select dbo.fn_basetodec('100000110001011100001010110110010010101100', 2),
dbo.fn_basetodec('20C5C2B64AC', 16),
dbo.fn_basetodec('B66ACAEE5A', 18),
dbo.fn_basetodec('SQLROCKS', 36)
-------------------- -------------------- -------------------- --------------------
2252109210796 2252109210796 2252109210796 2252109210796
(1 row(s) affected)
There's always more than one way to skin a cat
/Kenneth
-
Very, very cool... ol' Itzik did a neat job on this one.
Ken, do you know of a fn_DecToBase function in a similar vein by anyone?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Of course, Itzik made one for that too.
if object_id('dbo.fn_dectobase') is not null drop function dbo.fn_dectobase
go
create function dbo.fn_dectobase(@val as bigint, @base as int)
returns varchar(63)
as
-- by Itzik, SQL Server mag July 2005 issue.
begin
declare @r as varchar(63),
@alldigits as varchar(36)
set @alldigits = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'
set @r = ''
while ( @val > 0 )
begin
set @r = substring(@alldigits, @val % @base + 1, 1) + @r
end
return @r
end
go
/Kenneth
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply