MSQL Query Get the Int value of a Hex value of an Integer

Question

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.

 field       | type
 ________________________
  id         | int
  label      | int
  account_id | int

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("X")

However My problem exists with the existing entries int he database before I added the new label field. I have found several questions with a similar but do not answer my question

• Converting a paragraph to hex notatation, then back to string
• Converting a String to HEX in SQL
• 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 0x11) 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 exiting id's 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.

 SELECT CONVERT(INT, '0x' + id) FROM cp;

I can get the correct effect in C# by doing the following

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

UPDATE: I have found the solution to this problem for my use-case but some confusion has been raised that I am going to be doing all of my conversion in the database, I assume by the SELECT statement above. To clarify that this is a one-time event to convert existing records. Note the below statement does not work but stackoverflow will not let me edit the original statement as the change is less than 15 characters.

 UPDATE [dbname].[dbo].[cp] set [Label] = CONVERT(INT, '0x' + id); 

Answer 1

Found the Answer, it's not pretty but it solves my problem so I thought I would share it with you. If anyone has a simpler solution that would be great but in the mean time here we go.

Create a MSQL Function

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 simply execute the following query

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

This solution was discovered in http://dpatrickcaldwell.blogspot.com/2009/05/converting-hexadecimal-or-binary-to.html

Answer 2

You should NOT be doing your number formatting in SQL. Let the ints come out of the database as ints, and format them in your presentation layer. You completely haven't mentioned what that presentation layer is, so it's impossible to say more, but if you do this, you're DOOMED when your boss comes over and says that some users want to see the hexadecimal, and some want to see base-10.