kamui.co.uk

MSQL Getting the Integer Value from a Hex Value that looks like a Decimal

Background

Just to give you come context I have included a little background as to why I’m trying to do this:

I have a table ‘cp’ which has an [id] field which is the identifier for each registration I have come realize that this is to be moved from a deployed solution to a cloud based as a result multiple accounts will be accessing the same objects and it would be best if they all start at 1 so rather than use the id field I have created an int field called label which will be used in conjunction with the account id to find the unique record per account.

When the registrations are printed out on the screen I want them to be displayed as a 5 digit HEX number and I can simply do the following in the c# code

value.ToString(“X5″)

However the problem existed with the existing entries in the database before I added the new label field. I have found several questions on StackOverflow with a similar topic but did not answer my question

• Converting a paragraph to hex notatation, then back to string
• Converting a String to HEX in SQL
• SQL Query convert integer to hex and hex to integer

The Problem

i have the fields with ids (1,2,3,—-,11,12,13,—etc) values 1-9 can simply be copied over as the value.ToString(“X”) will simply do nothing with those values however if I simply copied value 11 would return “B” which correctly is the HEX value of 11 however I want it to return “11″ so I need an SQL script which will convert 11 => 17 (which is the hex value of 0×11) so that when the application reads it it will output the value 11 to the screen.

This is only to occur once to convert the existing ids, which is why I’m wanting an SQL script to do it all in one batch rather than build it into the application.

What I would Like would be something like below so that it assumes the value in would be a hex value and convert it back to an Int.

UPDATE [cp] SET label_id = CONVERT(INT, ’0x’ + id);

I found that I could get the correct effect in C# by doing the following

int.Parse(value.ToString(),System.Globalization.NumberStyles.HexNumber).ToString()

The Solution

I asked this question on StackOverflow But as it turned out I managed to find a solution before anyone answered the question.

It was not the prettiest answer but could be used for multiple use-cases and I liked the simplicity of it.

CREATE FUNCTION ConvertFromBase  
(  
    @value AS VARCHAR(MAX),  
    @base AS BIGINT  
) RETURNS BIGINT AS BEGIN  

-- just some variables  
DECLARE @characters CHAR(36),  
        @result BIGINT,  
        @index SMALLINT;  

-- initialize our charater set, our result, and the index  
SELECT @characters = '0123456789abcdefghijklmnopqrstuvwxyz',  
       @result = 0,  
       @index = 0;  

-- make sure we can make the base conversion.  there can't  
-- be a base 1, but you could support greater than base 36  
-- if you add characters to the @charater string  
IF @base < 2 OR @base > 36 RETURN NULL;  

-- while we have characters to convert, convert them and  
-- prepend them to the result.  we start on the far right  
-- and move to the left until we run out of digits.  the  
-- conversion is the standard (base ^ index) * digit  
WHILE @index < LEN(@value)  
    SELECT @result = @result + POWER(@base, @index) *   
                     (CHARINDEX  
                        (SUBSTRING(@value, LEN(@value) - @index, 1)  
                        , @characters) - 1  
                     ),  
           @index = @index + 1;  

-- return the result  
RETURN @result;  
END  

Then it can simply be called using

UPDATE [cp] SET label = dbo.ConvertFromBase(id,16);

I would like to thank the original author of this code Converting Hexadecimal or Binary to Decimal on SQL Server 2008 and 2005, though I am not using it exactly at the writer intended it is brilliantly simple.