Not able to convert directly to decimal value?

Question

I have the following string and want to convert it to DECIMAL(38,0):

a321

The following code is OK:

SELECT CAST(CONVERT(binary(2), 'a321', 2) AS BIGINT);  -- 41761

but this one fails:

SELECT CAST(CONVERT(binary(2), 'a321', 2) AS DECIMAL(38,0));

Msg 8114, Level 16, State 5, Line 7 Error converting data type varbinary to numeric.

It is not a big deal to do two casts like this:

SELECT CAST(CAST(CONVERT(binary(2), 'a321', 2) AS BIGINT) AS DECIMAL(38,0));

but I want to know why is not working. Can anyone explain?

Answer 1

Convert to a bigint first and then convert to a decimal:

SELECT CONVERT(DECIMAL(38, 0), CONVERT(BIGINT, CONVERT(binary(2), 'a321', 2)))

The binary representation of decimals is quite different from integers, and not all binary representations can be converted to a decimal.