Introduction
I've been looking around to convert a string containing a hexadecimal value to an integer value using Microsoft SQL and indeed found some articles on the internet. Some of the articles convert an Hexadecimal value and not a string. Some of them do convert a string but use loops that convert each char separately and add the values to get a result. So I thought there had to be a proper way to do this by still using the Microsoft SQL convert function.
Background
So maybe you're wondering why I should convert this data. At our company, we have to deal with hardware, such as retrieving and sending messages, most of them containing hexadecimal values. Those messages are stored as raw data in a table. This table is the buffer between the user interface and hardware. Doing it this way ensures that we do not lose any data and makes the hardware available from any place and from any user interface as long as it supports a database connection. Further the data has to be parsed to make it representable for the users or for other functionality.
Using the code
This code is quite simple to use as if you execute it from the Microsoft SQL query analyzer or Server management studio if you're using Microsoft SQL 2005. It creates a stored procedure called Stp_HexToInt
and needs only one parameter. The return value of the stored procedures contains the value as an integer.
The stored procedure itself is as follows:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE Stp_HexToInt
@HexValue Varchar(20)
AS
BEGIN
Declare @Query nvarchar(100)
Declare @Parameters nvarchar(50)
Declare @ReturnValue Int
if (charindex('0x',@HexValue) = 0)
Set @HexValue = '0x' + @HexValue
Set @Query = N'Select @Result = Convert(int,' + @HexValue + ')'
Set @Parameters = N'@Result int output'
Exec master.dbo.Sp_executesql @Query, @Parameters, @ReturnValue Output
RETURN @ReturnValue
END
GO
So what is this code doing?
- Adding a prefix to tell Microsoft SQL that it is an hexadecimal type in case the string is not delivered in a proper hexadecimal value.
FF
will be changed to 0xFF
.
if (charindex('0x',@HexValue) = 0)
Set @HexValue = '0x' + @HexValue
- Building the
Query
in runtime containing your hexadecimal value, but not as a string anymore. Now it becomes a real hexadecimal value in your Query.
Set @Query = N'Select @Result = Convert(int,' + @HexValue + ')'
- Creating the
result
parameter as output for the sp_executeSql
Stored procedure.
Set @Parameters = N'@Result int output'
- And at last, executing your own build
query
that converts the hexadecimal value and returns an integer in the @ReturnValue
parameter.
Exec master.dbo.Sp_executesql @Query, @Parameters, @ReturnValue Output
Testing the stored procedure
Of course this is already known, but for the beginners, here's how to test it.
Declare @Result int
execute @Result = stp_HexToInt '0xFF'
print @result
Points of Interest
Because Microsoft SQL can only convert a hexadecimal value, and not a string containing a hexadecimal value, I thought that building the code at runtime and executing it with Execute(@SQL)
could solve this problem, but the problem here is that it doesn't have a return value or parameters (unless someone else can prove the opposite). So I had to find a way to get a result from my own build Query
.
Believe it or not, the books online can still provide some help and told me that Sp_executesql
supports parameters when executing your own build Query
(in runtime). So there was my solution. Do not forget that some parameters needs "output", otherwise they are not assigned.