SQL Server Performance

Help with HEX

Discussion in 'SQL Server 2005 General Developer Questions' started by sql_jr, Jun 14, 2007.

  1. sql_jr New Member

    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 />
  2. FrankKalis Moderator

    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>
  3. sql_jr New Member

    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
  4. Madhivanan Moderator


    declare @t int
    set @t=138
    select cast(@t as varbinary(100))


    Madhivanan

    Failing to plan is Planning to fail
  5. FrankKalis Moderator

    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
  6. sql_jr New Member

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

    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
  8. satya Moderator

    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.
  9. sql_jr New Member

    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=':)' />
  10. FrankKalis Moderator

    <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>
  11. sql_jr New Member

    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 />
  12. Adriaan New Member

    Hm, reminds me of the bitmask for updated columns in triggers, where you also may have to use SUBSTRING.
  13. sql_jr New Member

    Adriaan --- do tell what you know. Do you have any knowledge of these patterns?
    How does that work?

    Awaiting for reply....Many thanks!
  14. Adriaan New Member

    Check the CREATE TRIGGER syntax in BOL, especially the bits about COLUMNS_UPDATED().
  15. sql_jr New Member

    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?
  16. sql_jr New Member

    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!
  17. FrankKalis Moderator

    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>

Share This Page