Update 2012-12-17
Fast conversion INT to HEX , without UDF or cross apply :
select right(convert( varchar(20), convert( varbinary(3), 16777215 ),1 ),6)
Update 2012-08-23 : this function is not necessary , it is much better to use :
declare @hex varchar(10) = 'F'
select CAST(CONVERT(VARBINARY, right('0000000000'+@hex,10) , 2) AS bigINT)
It does not require any UDF or cross-apply
=== Follows out to date ====
Let's have a large table Extract_MASTERQUEST_TDR containing field Transaction_Time_Hex varchar(10), representing hexadecimal Unix timestamp value.
We need to convert the hex value into numeric .
We could use a TSQL function, as described in http://www.sqlservercentral.com/scripts/Miscellaneous/30770/ but it will not perform fast on large tables.
This script uses a Table Valued Inline User Defined Function ( UDF), and it is much faster than a standard UDF. It simply calculates the value , using the definition of base 16 numbers.
It is simple concept : in base 10, for example, 234 = 4*(10^0)+3*(10^1)+2*(10^2)
Typical usage :
select * from Extract_MASTERQUEST_TDR a
cross apply dbo.HEX2BIGINT( right( '00000000'+Transaction_Time_Hex,10)) b
In this way, by the way, the large table Extract_MASTERQUEST_TDR will be scanned by nested loop . If we want to add some join , we could put the code that is into the UDF HEX2BIGINT straight into the select, letting the optimizer to use hash join
select Transaction_Time_Hex ,
cast(POWER(16,0) as bigint)*cast(CASE WHEN substring(Transaction_Time_Hex,LEN(Transaction_Time_Hex),1) LIKE '[0-9]'
THEN cast( substring(Transaction_Time_Hex,LEN(Transaction_Time_Hex),1) as bigint)
ELSE CAST(ASCII(UPPER(substring(Transaction_Time_Hex,LEN(Transaction_Time_Hex),1)))-55 as bigint)
END as bigint) +
cast(POWER(16,1) as bigint)*cast(CASE WHEN substring(Transaction_Time_Hex,LEN(Transaction_Time_Hex)-1,1) LIKE '[0-9]'
THEN cast( substring(Transaction_Time_Hex,LEN(Transaction_Time_Hex)-1,1) as bigint)
ELSE CAST(ASCII(UPPER(substring(Transaction_Time_Hex,LEN(Transaction_Time_Hex)-1,1)))-55 as bigint)
END as bigint) +
cast(POWER(16,2) as bigint)*cast(CASE WHEN substring(Transaction_Time_Hex,LEN(Transaction_Time_Hex)-2,1) LIKE '[0-9]'
THEN cast( substring(Transaction_Time_Hex,LEN(Transaction_Time_Hex)-2,1) as bigint)
ELSE CAST(ASCII(UPPER(substring(Transaction_Time_Hex,LEN(Transaction_Time_Hex)-2,1)))-55 as bigint)
END as bigint) +
cast(POWER(16,3) as bigint)*cast(CASE WHEN substring(Transaction_Time_Hex,LEN(Transaction_Time_Hex)-3,1) LIKE '[0-9]'
THEN cast( substring(Transaction_Time_Hex,LEN(Transaction_Time_Hex)-3,1) as bigint)
ELSE CAST(ASCII(UPPER(substring(Transaction_Time_Hex,LEN(Transaction_Time_Hex)-3,1)))-55 as bigint)
END as bigint) +
cast(POWER(16,4) as bigint)*cast(CASE WHEN substring(Transaction_Time_Hex,LEN(Transaction_Time_Hex)-4,1) LIKE '[0-9]'
THEN cast( substring(Transaction_Time_Hex,LEN(Transaction_Time_Hex)-4,1) as bigint)
ELSE CAST(ASCII(UPPER(substring(Transaction_Time_Hex,LEN(Transaction_Time_Hex)-4,1)))-55 as bigint)
END as bigint) +
cast(POWER(16,5) as bigint)*cast(CASE WHEN substring(Transaction_Time_Hex,LEN(Transaction_Time_Hex)-5,1) LIKE '[0-9]'
THEN cast( substring(Transaction_Time_Hex,LEN(Transaction_Time_Hex)-5,1) as bigint)
ELSE CAST(ASCII(UPPER(substring(Transaction_Time_Hex,LEN(Transaction_Time_Hex)-5,1)))-55 as bigint)
END as bigint) +
cast(POWER(16,6) as bigint)*cast(CASE WHEN substring(Transaction_Time_Hex,LEN(Transaction_Time_Hex)-6,1) LIKE '[0-9]'
THEN cast( substring(Transaction_Time_Hex,LEN(Transaction_Time_Hex)-6,1) as bigint)
ELSE CAST(ASCII(UPPER(substring(Transaction_Time_Hex,LEN(Transaction_Time_Hex)-6,1)))-55 as bigint)
END as bigint) +
cast(POWER(16,7) as bigint)*cast(CASE WHEN substring(Transaction_Time_Hex,LEN(Transaction_Time_Hex)-7,1) LIKE '[0-9]'
THEN cast( substring(Transaction_Time_Hex,LEN(Transaction_Time_Hex)-7,1) as bigint)
ELSE CAST(ASCII(UPPER(substring(Transaction_Time_Hex,LEN(Transaction_Time_Hex)-7,1)))-55 as bigint)
END as bigint) +
POWER(cast(16 as bigint),8) *cast(CASE WHEN substring(Transaction_Time_Hex,LEN(Transaction_Time_Hex)-8,1) LIKE '[0-9]'
THEN cast( substring(Transaction_Time_Hex,LEN(Transaction_Time_Hex)-8,1) as bigint)
ELSE CAST(ASCII(UPPER(substring(Transaction_Time_Hex,LEN(Transaction_Time_Hex)-8,1)))-55 as bigint)
END as bigint) +
POWER(cast(16 as bigint),9) *cast(CASE WHEN substring(Transaction_Time_Hex,LEN(Transaction_Time_Hex)-9,1) LIKE '[0-9]'
THEN cast( substring(Transaction_Time_Hex,LEN(Transaction_Time_Hex)-9,1) as bigint)
ELSE CAST(ASCII(UPPER(substring(Transaction_Time_Hex,LEN(Transaction_Time_Hex)-9,1)))-55 as bigint)
END as bigint) as Transaction_Time_Bigint from Extract_MASTERQUEST_TDR a left join .... sth else
