convert from hexadecimal to a decimal

From: Skip (anonymous_at_discussions.microsoft.com)
Date: 09/29/04 

Date: Tue, 28 Sep 2004 18:43:44 -0700

We have a tableA with a varchar field whose contents are
hexadecimal. We want to insert these hexadecimal contents
from a varchar field into different table, tableB in
decimal format.

We tried to use Cast and convert functions to explicitly
convert into int field thinking it will give us right
decimal, which didn't work.

Example of what works:
Select CAST(Cast(cast(0x2A as varchar) AS varbinary) AS
int) returns value of 42.

This doesn't work:
Select CAST(Cast(cast('0x2A' as varchar) AS varbinary) AS
int)

All the values coming from tableA are in '0x2A' form
because the field is defined as a varchar. We can set the
field name in tableB to whatever we want (int, decimal or
even varbinary)

Could someone please pointers / suggestions on how to
convert hexadecimal to int or even how to remove the
leading and trailing ' ?

Thanks in advance

Relevant Pages