Convert hex string to bigint

from http://www.idilks.com/mssql/md5







-- select md5 as a hex string (regular string)

SELECT master.sys.fn_varbintohexsubstring(0, HashBytes('MD5', 'SQL Centroid'), 1, 16)

-- convert a hex string from a string to varbinary(16)

SELECT CAST('' as xml).value('xs:hexBinary("2655a2b5ef3531c4fd330c9ab409afa9")', 'varbinary(16)')

-- convert a hex string from a variable to varbinary(16)

DECLARE @thestring CHAR(32)

SELECT @thestring = '2655a2b5ef3531c4fd330c9ab409afa9'

SELECT CAST('' AS XML).value('xs:hexBinary(sql:variable("@thestring"))', 'varbinary(16)')

-- convert a hex string from a varchar column to varbinary(16)

DECLARE @TABLEA TABLE (tID int IDENTITY(1,1) PRIMARY KEY, tst varchar(200))

INSERT INTO @TABLEA (tst) VALUES ('2655a2b5ef3531c4fd330c9ab409afa9')

SELECT CAST('' AS XML).value('xs:hexBinary(sql:column("t.tst"))', 'varbinary(16)')

FROM @TABLEA t




SELECT top 10

cast(CAST('' AS XML).value('xs:hexBinary(sql:column("f.crcString"))', 'varbinary(8)') as bigint)

,f.crcString

FROM 

filecompare f