--**************************************
--
-- Name: HEX string value to int convers
-- ion.sql
-- Description:Code to convert a hexadec
-- imal value stored in a string to integer
--
-- By: Umachandar
--
--This code is copyrighted and has-- limited warranties.Please see http://
-- www.Planet-Source-Code.com/xq/ASP/txtCod
-- eId.123/lngWId.5/qx/vb/scripts/ShowCode.
-- htm--for details.--**************************************
--
CREATE TABLE Numbers( number int )
-- where number is between 1 and 255 or
-- so.
DECLARE @t varchar (
SELECT @t = 'FFFFFFFF'
SELECT sum(
CASE lower( substring( hexstr , number , 1 ) )
WHEN '0' THEN 0
WHEN '1' THEN 1
WHEN '2' THEN 2
WHEN '3' THEN 3
WHEN '4' THEN 4
WHEN '5' THEN 5
WHEN '6' THEN 6
WHEN '7' THEN 7
WHEN '8' THEN 8
WHEN '9' THEN 9
WHEN 'a' THEN 10
WHEN 'b' THEN 11
WHEN 'c' THEN 12
WHEN 'd' THEN 13
WHEN 'e' THEN 14
WHEN 'f' THEN 15
END * convert( decimal( 28 , 0 ) , power( 16 , number - 1 ) )
)
FROM
(
SELECT reverse( @t ) AS hexstr
) AS h1
join
Numbers n
ON number BETWEEN 1 AND len( hexstr )
go