Home
MS SQL Hexadecimal String to Integer
Blog Date 23 February 2023
--Create a @start string with a hexadecimal value
DECLARE @mystring VARCHAR(MAX);
set @mystring = '0x0550DC';
--Now remove the "x" from the string
set @mystring = REPLACE(@mystring,'x','');
-- leaving '00550DC'
--Apparently we MUST use a string with an even number or characters. We add *some* zeros then
--take what we need from the right side
set @mystring = RIGHT('000000000' + @mystring, 8);
-- makes '0000000000550DC' then takes 8 chars from the right giving '000550DC'
--Now we have an even number of characters we can replace the '0x' What a FAFF
set @mystring = '0x' + @mystring
-- gives us'0x000550DC'
--Create an integer to accept the hex string
DECLARE @myint INT
--for reasons unknown this last task has to be done as one.
--We can not do the convert to varbinary THEN the cast to integer seperately.
--here we say 'convert to varbinary, then cast to int'
--the CONVERT has the third value '1' explained under Binary Styles here https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver16
set @myint = CAST( CONVERT(varbinary, @mystring, 1) AS INT)
select @myint;
If you run the above code and read the notes it'll make some sense. This can all be "wrapped up" into one line
DECLARE @mystring VARCHAR(MAX);
set @mystring = '0x0550DC';
DECLARE @myint INT
set @myint = CAST( CONVERT(varbinary, '0x' + RIGHT('000000000' + REPLACE(@mystring,'x',''), 8), 1) AS INT)
select @myint;
Reader's Comments
Name
Comment
Add a RELEVANT link (not required)
Upload an image (not required)
Uploading...
Home