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