I adapted the following from this link (https://social.msdn.microsoft.com/Forums/en-US/20e92e10-a0ab-4a53-a766-76f84bfd4e8c/converting-hex-values-to-int?forum=transactsql).
It makes use of a "dummy" XML CAST so as to be able to use ".value".
I CAST the resulting hex from the string column value to INT since I needed to do this, but you can easily remove that part if desired.
(substitute your column name for col1, and of course supply your table/view after the final FROM):
SELECT
CAST
(
(
SELECT
CAST ('' AS XML).value('xs:hexBinary(substring(sql:column("col1"), sql:column("t.pos")) )', 'varbinary(max)')
FROM
(
SELECT
CASE SUBSTRING (col1, 1, 2)
WHEN '0x'
THEN 3
ELSE 0
END
)
AS t(pos)
)
AS INT
)
FROM