Help with HEX | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Help with HEX

hi, SSP Gurus:<br /><br /> I have some data in some columns stored as HEX. When the col type is nvarchar, I can convert the hex string using select cast(0xMyHexValue as nvarchar(400)). On other columns where it is varchar, in some cases cast(0xMyHexValue as varchar(400)) works.<br /><br />In other cases, when I use a hex calculator, I see that within the HEX string if I eliminate x number of characters and then use the conversion – but since I’m not a HEXpert (pun intended[<img src=’/community/emoticons/emotion-2.gif’ alt=’:D‘ />]), there must be something I’m missing.<br />I see in varchar there is a 16 chr offset, what is this?<br /><br />Also, how would I convert dates and integers from a HEX value?<br /><br />Many thanks!<br /><br />
Suppose you have the hex value of 0x0000008A. Checking with the Windows calculator you will see that it is 138 in decimal. Now look at this weird results:<br /><pre id="code"><font face="courier" size="2" id="code"><br />SELECT CAST(CAST(‘0x0000008A’ AS VARBINARY) AS INT)<br /><br />———–<br />808466497<br /><br />(1 row(s) affected)<br /></font id="code"></pre id="code"><br />and<br /><pre id="code"><font face="courier" size="2" id="code"><br />SELECT CONVERT( INT, CONVERT( VARBINARY, ‘0x8A’ ) ) <br /><br />———–<br />813185089<br /><br />(1 row(s) affected)<br /></font id="code"></pre id="code"><br />Both are wrong. When you explicitely use a VARBINARY(n) in the statements above, the result changes with the length of the VARBINARY. It is kind of funny. I have no idea why it is as it is. But here’s a workaround:<br /><pre id="code"><font face="courier" size="2" id="code"><br />DECLARE @hex VARCHAR(10)<br />DECLARE @stmt NVARCHAR(255)<br />DECLARE @int INT <br />SET @hex = ‘0x0000008A’ <br />SELECT @stmt = N’SELECT @int = CONVERT( int , ‘ + @hex + ‘ )’ <br />EXEC sp_ExecuteSql @stmt, N’ @int Int Out’, @int OUT SELECT @int<br /><br />———–<br />138<br /><br />(1 row(s) affected)<br /></font id="code"></pre id="code"><br />Now, with DATETIME it is a bit [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />] easier as DATETIME is a BINARY(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> behind the scenes anyway:<br /><pre id="code"><font face="courier" size="2" id="code"><br />DECLARE @hex BINARY(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /><br />DECLARE @stmt NVARCHAR(255)<br />DECLARE @dt DATETIME <br />SET @dt = GETDATE()<br />SET @hex = CAST(@dt AS BINARY(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />)<br />SELECT CAST(@hex AS DATETIME), @dt<br /><br /> <br />———————– ———————–<br />2007-06-14 20:59:50.027 2007-06-14 20:59:50.027<br /><br />(1 row(s) affected)<br /></font id="code"></pre id="code"><br /><br />–<br />Frank Kalis<br />Microsoft SQL Server MVP<br />Contributing Editor, Writer & Forum Moderator<a target="_blank" href=http://www.sql-server-performance.com>http://www.sql-server-performance.com</a><br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
thx much, Frank. However, I purposely entered 138 as your example, and it shows up in the hex column as ‘0x8A0000000700D8’, as opposed to ‘0x0000008A. So, I get a diff value w/your script. Any ideas why?. You obviously see the 8A, but the order is doff/ Please assist. tx

declare @t int
set @t=138
select cast(@t as varbinary(100))
Madhivanan Failing to plan is Planning to fail
quote:Originally posted by sql_jr thx much, Frank. However, I purposely entered 138 as your example, and it shows up in the hex column as ‘0x8A0000000700D8’, as opposed to ‘0x0000008A. So, I get a diff value w/your script. Any ideas why?. You obviously see the 8A, but the order is doff/ Please assist. tx
I have no idea what you did and how you did it, but ‘0x8A0000000700D8’ is a VERY large INT number. Where does this value come from? —
Frank Kalis
Microsoft SQL Server MVP
Contributing Editor, Writer & Forum Moderatorhttp://www.sql-server-performance.com
Webmaster:http://www.insidesql.de
Thx for replies. Still need help. Madhivanan – your solution gets the int value to the hex, but I wouldn’t have that value, so I need the conversion the other way (in general) Frank, indeed that’s the value that’s output in the table.
(OK, its a value that MS stores in one of its systables, but want to convert it)
Do they have some sort of algorithm I’m not getting, or they purposely padding the converted value. Thx again
Which systable do you have in mind?
They like to flip bits and bytes. Probably you need to use SUBSTRING and put the pieces back together in some different order —
Frank Kalis
Microsoft SQL Server MVP
Contributing Editor, Writer & Forum Moderatorhttp://www.sql-server-performance.com
Webmaster:http://www.insidesql.de
Is it possible know from which table you need such values?
Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
I guess its silly to ask, but can one derive a possible pattern getting ‘0x8A0000000700D8’, to this: ‘0x0000008A’?<br /><br />For example if I knew it was always 6 zeroes, I can do an operation to derive the right hex value, and convert it, or am I knocking my head against the wall of improbability? <img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />
<pre id="code"><font face="courier" size="2" id="code"><br />DECLARE @hex VARCHAR(10)<br />DECLARE @stmt NVARCHAR(255)<br />DECLARE @int INT <br />SET @hex = SUBSTRING(‘0x8A0000000700D8′ ,1,<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /><br />SET @hex = SUBSTRING(@hex,1,2)+ SUBSTRING(@hex,7,2)+SUBSTRING(@hex,5,2)+SUBSTRING(@hex,3,2)<br />SELECT @stmt = N’SELECT @int = CONVERT( int , ‘ + @hex + ‘ )’ <br />EXEC sp_ExecuteSql @stmt, N’ @int Int Out’, @int OUT <br />SELECT @int<br /></font id="code"></pre id="code"><br /><br />–<br />Frank Kalis<br />Microsoft SQL Server MVP<br />Contributing Editor, Writer & Forum Moderator<a target="_blank" href=http://www.sql-server-performance.com>http://www.sql-server-performance.com</a><br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
Well, that’s cool and works like a charm — for ‘138’ <img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />. <br /><br />Interesting, I notice when I changed the value from 1224 to 1225=(0x000004C9), the stored value is 0xC9<br /><br /><br />I do appreciate your quick reply, but I guess the only ones who know are in Seattle, WA :-(. You’re the best! Thx. <br /><br />
Hm, reminds me of the bitmask for updated columns in triggers, where you also may have to use SUBSTRING.
Adriaan — do tell what you know. Do you have any knowledge of these patterns?
How does that work? Awaiting for reply….Many thanks!
Check the CREATE TRIGGER syntax in BOL, especially the bits about COLUMNS_UPDATED().
Ok, this may be a wild goose chase, but here is the bol text – do you think this algorithm may apply? The bitmask is: power(2,(2-1))+power(2,(3-1))+power(2,(4-1)) = 14 How could I test this?
I guess the bitmask thingy might be a red-herring. BUT, Frank, the thing about flippin bits! I am pretty warm on converting integers now, as I have derived a pattern!
I see that the # of 0’s depends on whether its one digit, two digits, three, etc.
(ex: 10, 138, 1040) The real trick now is knowing the length of the true integer value, when all you have is the hex value. Ideas? If I knew how to derive that somewhere, I think I may have it!!
Let me work on something and post back….Inspirational!
My reply was just a quick shot. [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br />But this trigger stuff rings a bell:<a target="_blank" href=http://www.umachandar.com/technical/SQL70Scripts/Main36.htm>http://www.umachandar.com/technical/SQL70Scripts/Main36.htm</a><br />I just thought of this bit flipping, because you need to use it when deriving the actual file# and page# values from the ‘first’ column in sysindexes for DBCC PAGE.<br /><br />–<br />Frank Kalis<br />Microsoft SQL Server MVP<br />Contributing Editor, Writer & Forum Moderator<a target="_blank" href=http://www.sql-server-performance.com>http://www.sql-server-performance.com</a><br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
]]>