How can I convert a varchar with hexadecimal value to int?

Question

I need to convert a VARCHAR with a hex number to INT, I tried:

DECLARE @H VARCHAR(2);
SELECT @H = '9a'
SELECT CONVERT(INT, 0x + @H)

But it says @H has to be a VARBINARY, and if I cast I get a completely different number.

From '9a' I want to get 154, how can I achieve it?

Answer 1

Looks like this guy will get you to a varbinary, then you can just use the CONVERT function.

It looks like you can do it natively with XML. WTF Micsrosft XML?

Answer 2

See the ConvertFromBase user defined function here

Example usage select dbo.ConvertFromBase('196', 16) (Returns 406)

Answer 3

Declare @HexValue varchar(10)
Set @HexValue = '9a'

Declare @Sql nvarchar(max)
Declare @Value int

Set @Sql = 'Select @Value = Cast(Cast(0x' + @HexValue + ' as varbinary(4)) As int)'
exec sp_executesql @Sql, N'@Value int OUTPUT', @Value OUTPUT

Select @Value