In one of the recent projects, I realize the bottleneck of the query was an inline function which was converting Hex to Decimal. I optimized the inline function and reduced the query running time to one-tenth of the original running time. Later, I was eager to find out the script my blog readers might be using for hex to decimal conversion. Please leave your comments here and I will consider all the valid answers and publish with due credit to the author in one of the future posts. If the script you have posted here is not your original script, I suggest that you include the source as well.
Reference: Pinal Dave (http://blog.SQLAuthority.com)
Here is one way to do it:
create function fn_HexToIntnt(@str varchar(16))
returns bigint as begin
select @str=upper(@str)
declare @i int, @len int, @char char(1), @output bigint
select @len=len(@str)
,@i=@len
,@output=case
when @len>0
then 0
end
while (@i>0)
begin
select @char=substring(@str,@i,1), @output=@output
+(ASCII(@char)
-(case
when @char between ‘A’ and ‘F’
then 55
else
case
when @char between ’0′ and ’9′
then 48 end
end))
*power(16.,@len-@i)
,@i=@i-1
end
return @output
end
hmm..
What about using built-in function Convert?
SELECT CONVERT(INT, 0×00000100)
SELECT CONVERT(VARBINARY(8), 256)
I do not pretend to be the author, but i am using this for a long time
I’ve lately interested in Sql Server’s CLR support so here goes.
First I enable CLR support on Sql Server.
EXEC sp_CONFIGURE ‘clr enabled’ , ’1′
GO
RECONFIGURE;
GO
Then I create a simple DLL assembly called DBClasses with a static class named StringFormatFunctions and a static method HexToInt.
public partial class StringFormatFunctions
{
[SqlFunction(IsDeterministic = true)]
public static int HexToInt(string input)
{
return int.Parse(input, System.Globalization.NumberStyles.HexNumber);
}
}
Next I load it to Sql Server.
CREATE ASSEMBLY DBClasses
FROM ‘C:\path\DBClasses.dll’
WITH PERMISSION_SET = SAFE
Finally I create a little function for it.
CREATE FUNCTION HexToInt(@input nvarchar)
RETURNS int
AS
EXTERNAL NAME DBClasses.StringFormatFunctions.HexToInt
And now I can start to use it.
SELECT dbo.HexToInt(‘ffff’)
If it is integer make use of implicit convertion
SELECT CONVERT(VARBINARY(8), 659604),0x000A1094*1
If it is decimal, use cast function
SELECT CONVERT(VARBINARY(8), 659604.1),cast(0x07010001C9A56400 as decimal(12,2))
http://beyondrelational.com/blogs/madhivanan
If you are using SQL Server 2008, you can simply use the built-in function CONVERT, with the style 1:
SELECT CONVERT(varchar(100),0x123456789abc123,1)+’?’
SELECT CONVERT(varbinary,’0x0123456789ABC123′,1)+0×0
Notice that on SQL Server 2005, the above queries will not yield an error, but you will get different results (the same as if we used style 0).
Source: http://msdn.microsoft.com/en-us/library/ms187928.aspx
Razvan
http://beyondrelational.com/blogs/madhivanan/archive/2010/02/03/converting-hexadcimal-to-decimal.aspx
OK, I’m going to assume that we are starting with a string holding hex digits, and we wnat to convert that into a int.
declare @hex varchar(10);
set @hex = ‘BD12′;
declare @retval int;
set @reval = — 48402 via some magic.
Now, this being the wacky world of SQL, the best solution is gonna be something involving a set operation on tables, so let’s start with a permanaent table mapping hex digits to their values:
CREATE TABLE [HexDigits](
[Digit] [char](1) NOT NULL,
[Value] [int] NOT NULL,
CONSTRAINT [PK_HexDigits] PRIMARY KEY CLUSTERED ([Digit] ASC) )
INSERT INTO [HexDigits] VALUES (’0′, 0);
INSERT INTO [HexDigits] VALUES (’1′, 1);
– etc
INSERT INTO [HexDigits] VALUES (‘E’, 14);
INSERT INTO [HexDigits] VALUES (‘F’, 15);
Then, given a similar (presumably temporary) table mapping digits in the string we want translated to their digit position (count from the right), such as this,
CREATE TABLE [HexString](
[Digit] [char](1) NOT NULL,
[Pos] [int] NOT NULL
CONSTRAINT [PK_HexString] PRIMARY KEY CLUSTERED ([Digit] ASC) )
INSERT INTO [HexString] VALUES (‘B’, 4);
INSERT INTO [HexString] VALUES (‘D’, 3);
INSERT INTO [HexString] VALUES (’1′, 2);
INSERT INTO [HexString] VALUES (’2′, 1);
Then we have the simple query
select SUM(POWER(16,(pos-1))*Value)
From HexString s, HexDigits d
where s.Digit = d.Digit
The trick here will be the convert the original string (“BD12″, in this example), into the rows if the HexString table. I don’t know of a good way to do that, but I’m sure someone knows a simple way to do that.
There is no ‘hex’ data type in sql server that we would convert from or to. We have hex literal (eg. 0xFFFF) which is int data type. We can have hex digits stored as ascii characters in varchar (e.g. ‘FFFF’, bytes 70,70,70,70 ), or as binary digits in varbinary (bytes 255, 255). You did not specify which sql type did you really want to convert from. If you want to convert from hex string to integer number, here it is:
DECLARE @hexstr VARCHAR(10); SET @hexstr = ‘ffff’
DECLARE @rez BIGINT; SET @rez = 0
WHILE @hexstr ”
BEGIN
SET @rez = @rez * 16 + CHARINDEX(LEFT(@hexstr,1),’0123456789ABCDEF’) – 1
SET @hexstr = SUBSTRING(@hexstr,2,100)
END
PRINT @rez
Without loop, in one SELECT statement:
DECLARE @hexstr VARCHAR(10); SET @hexstr = ‘ffff’
DECLARE @rez BIGINT;
SELECT @rez = ISNULL(@rez,0) * 16 + CHARINDEX(substring(@hexstr,n.number+1,1),’0123456789ABCDEF’) – 1
FROM MASTER..spt_values n WHERE n.TYPE=’P’ AND n.number<len(@hexstr)
PRINT @rez
Vedran Kesegic
Hi guys,
Here’s an online tool I use to convert hex to decimal: hex to decimal converter
Pretty cool!
David
Hi All,
Please answer this,
select convert(varbinary(2),unicode(N’B'))
go
declare @tmp_var varchar(10)
SET @tmp_var = convert(varbinary(2),unicode(N’B'))
select @tmp_var ‘tmp_var’
Execute this query….
When a varbinary value is assigned to a varchar type, then it shows NULL. Why???
By
Biju.K.S
You can’t do that. Why do you want to do that?
No practical use..!
I just want to know that…
Thanks for your reply..:)
Hi,
please run this,
select convert(varbinary(2),unicode(N’B’))
go
declare @tmp_var varchar(10)
SET @tmp_var = sys.fn_varbintohexstr(convert(varbinary(2),unicode(N’B’)))
select @tmp_var ‘tmp_var’
;)
Thanks,
Biju
Hi,
please run this,
select convert(varbinary(2),unicode(N’B'))
go
declare @tmp_var varchar(10)
SET @tmp_var = sys.fn_varbintohexstr(convert(varbinary(2),unicode(N’B')))
select @tmp_var ‘tmp_var’
;)
Thanks,
Biju
A Little late to the party, however when reading books online for convert I noticed you could apply styles when converting to binary.
SELECT CONVERT(VARBINARY(8), 65535),CAST(0x0000FFFF AS INT),
– If the value is a string without the 0x
CAST(CONVERT(VARBINARY, ‘ffff’, 2) AS INT),
CAST(CONVERT(VARBINARY, ’0000ffff’, 2) AS INT),
– or if your string has the 0x
CAST(CONVERT(VARBINARY, ’0x0000FFFF’, 1) AS INT),
CAST(CONVERT(VARBINARY, ’0xFFFF’, 1) AS INT)
0x0000FFFF,65535,65535,65535,65535,65535
building on Ray’s contribution above, here is the generic case:
declare @hex varchar(64) = ’0x00FF’
select cast( CONVERT(VARBINARY,’0x’+right(’00000000′+replace(@hex,’x',”),8),1) as int)
This could easily be made into a function.
Moderator, please remove the “commented” values after the declare statement above. The two comment dashes got replaced by another symbol. Thanks