CREATE TABLE #HexConvertTbl (Val TINYINT, Hex CHAR(1) PRIMARY KEY )
INSERT #HexConvertTbl ( Val, Hex )
SELECT 0, '0'
UNION SELECT 1, '1'
UNION SELECT 2, '2'
UNION SELECT 3, '3'
UNION SELECT 4, '4'
UNION SELECT 5, '5'
UNION SELECT 6, '6'
UNION SELECT 7, '7'
UNION SELECT 8, '8'
UNION SELECT 9, '9'
UNION SELECT 10, 'A'
UNION SELECT 11, 'B'
UNION SELECT 12, 'C'
UNION SELECT 13, 'D'
UNION SELECT 14, 'E'
UNION SELECT 15, 'F'
--I hope you already have a Tally Table, just in case, here is a short one.
--The tally table is for position counting
CREATE TABLE #TallyTBL (N INT PRIMARY KEY) 
INSERT #TallyTBL ( N )
SELECT 1
UNION SELECT 2
UNION SELECT 3
UNION SELECT 4
UNION SELECT 5
UNION SELECT 6
UNION SELECT 7
UNION SELECT 8
UNION SELECT 9
UNION SELECT 10
UNION SELECT 11
UNION SELECT 12
UNION SELECT 13
UNION SELECT 14
UNION SELECT 15
UNION SELECT 16
UNION SELECT 17
UNION SELECT 18
UNION SELECT 19
UNION SELECT 20

--Ideally these would be parameters of a function
DECLARE @Hex varchar(16), @Len TINYINT

--Sample data, hopefully this gets passed in as a parameter
--You might have to clean up 0xFFFFFF format, but that should be easy enough
SELECT @Hex = 'F270AF'

--Limit for the number of rows the tally table split allows
SELECT @Len = LEN(@Hex)

--Summarize the values of each column
SELECT SUM(Q1.ColumnVal) as DecimalVal
FROM (
	--This is where the real work is done.  For column values discussion see: http://www.codemastershawn.com/library/tutorial/hex.bin.numbers.php
	--For Tally table split reference, see: http://www.sqlservercentral.com/articles/T-SQL/62867/
	SELECT SUBSTRING(@hex,N,1) Hex, h.Val, POWER(16,@Len-N)*h.Val AS ColumnVal
	FROM #TallyTBL t
	JOIN #HexConvertTbl h
	ON h.Hex = SUBSTRING(@hex,N,1)
	WHERE N <= @Len
) AS Q1

DROP TABLE #HexConvertTbl
DROP TABLE #TallyTBL