Convert a Hexadecimal to int using Microsoft SQL

• Download demo project - 0.2 KB
• Download source file - 0.5 KB

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
-- =============================================
-- Author: KC
-- Create date: 20/04/07
-- Description: Convert Hex value to int
-- =============================================
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.
  SQL

  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.
  SQL

  Set @Query = N'Select @Result = Convert(int,' + @HexValue + ')'
  

• Creating the result parameter as output for the sp_executeSql Stored procedure.
  SQL

  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.
  SQL

  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.