Click here to Skip to main content
13,410,805 members (30,672 online)
Click here to Skip to main content
Add your own
alternative version


14 bookmarked
Posted 22 Apr 2007

Convert a Hexadecimal to int using Microsoft SQL

, 22 Apr 2007
Rate this:
Please Sign up or sign in to vote.
An article on how to convert a Hexadecimal to int using Microsoft SQL


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.


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:

-- =============================================
-- Author: KC
-- Create date: 20/04/07
-- Description: Convert Hex value to int
-- =============================================
    @HexValue Varchar(20)
    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

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.


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


About the Author

Software Developer (Senior) Farmad
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.

After five years as a software test engineer at Niko nv, i switched back to development. As a dotnet c# developer at farmad i'm using the latest technologies : vs2013, dotnet 4.5, Linq in combination with devexpress components aswel for GUI as for ORM (xpo)

You may also be interested in...


Comments and Discussions

Generalnew and easier way in SQL Server 2008 Pin
lirco14-Jul-10 2:20
memberlirco14-Jul-10 2:20 
GeneralSQL Server 2000/2005 allow to assign a hex-format integer... Pin
traceon12-Dec-08 1:27
membertraceon12-Dec-08 1:27 
GeneralAnd vice versa Pin
topcatalpha9-Jun-08 3:53
membertopcatalpha9-Jun-08 3:53 
Generalnice idea Pin
Thanks for all the fish12-Dec-07 6:17
memberThanks for all the fish12-Dec-07 6:17 

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

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

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.180221.1 | Last Updated 23 Apr 2007
Article Copyright 2007 by topcatalpha
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid