T-SQL Convert Hex to decimal

Question

I try to use TSQL and I get some problem to convert hex to decimal when the value is inside a variable.

My code is:

SET @hex = SUBSTRING(@x,1,2) --give 1e
SET @hex = '0x' + @hex
SELECT CAST (@hex AS int)

I get the error that the conversion failed when converting the varchar value to int

I don't know what it's wrong and so far I can't find solution

Any idea? Thanks

Answer 1

This works (for SQL Server 2008 or later):

declare @hex varchar(316)
SET @hex = '1e' --SUBSTRING(@x,1,2) --give 1e
SET @hex = '0x' + @hex
SELECT CONVERT(int,CONVERT (binary(1),@hex,1))

See "Binary Styles" on the CAST and CONVERT page.