Click here to Skip to main content
Click here to Skip to main content

Convert a Hexadecimal to int using Microsoft SQL

By , 22 Apr 2007
Rate this:
Please Sign up or sign in to vote.

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

topcatalpha
Software Developer (Senior) Niko nv
Belgium Belgium
After my studies i started as a technician for repair of consumer electronics at the philips company in Brussels, more specific laser, camcorder and PC products. This was not really software related but it gave me good knowledge about global architectures and bus systems.
 
In 1998 i started as delphi developer for 9 years, mainly for developing realtime auction software. So i’ve done a lot of programming for serial,network and internet communication, audiostreaming included. In the same period i’ve developed databases for those multitier applications on a MSSQL server.
 
August 2006 i’ve switched to the C# .net environment, writing applications for a company providing access control solutions. Though the developing was mainly for enduser software, i also had to write automatisation software.

Comments and Discussions

 
Generalnew and easier way in SQL Server 2008 Pinmemberlirco14-Jul-10 1:20 
GeneralSQL Server 2000/2005 allow to assign a hex-format integer... Pinmembertraceon12-Dec-08 0:27 
GeneralAnd vice versa Pinmembertopcatalpha9-Jun-08 2:53 
Generalnice idea PinmemberThanks for all the fish12-Dec-07 5:17 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.140415.2 | Last Updated 23 Apr 2007
Article Copyright 2007 by topcatalpha
Everything else Copyright © CodeProject, 1999-2014
Terms of Use
Layout: fixed | fluid