SQL Server Portal

I received a question from a blog reader “how to convert varbinary to numeric & vice versa & How SQL Server keeps the structure of numeric data type in varbinary format ?”

First lets take an example to convert from varbinary to numeric & vice versa”

Example :

--Convert from varbinary to numeric
Declare @varbinary2 as varbinary(max)
Set @varbinary2=0x08040001B471BC00
Select Convert(numeric(8,4),@varbinary2) as [Varbinary to Numeric]

--Convert from numeric to varbinary
Declare @Numeric2 as numeric(8,4)
Set @Numeric2 =1234.9876
Select Convert(varbinary(max),@Numeric2) as [Numeric to Varbinary]
--RESULT

Varbinary to Numeric
—————————————
1234.9876

(1 row(s) affected)

Numeric to Varbinary
—————————————
0x08040001B471BC00

(1 row(s) affected)

Explanation :
It is a simple conversion, but one thing you need to remember is, whenever you convert from varbinary to numeric, you must be exact in the Precision & Scale. If Precision & Scale are wrong, it will give you wrong result. But the question is how ascertain that the Precision & Scale are in varbinary format ?

Lets take an example from above and first get the Precision & Scale and then convert it into numeric data type.

--Convert from varbinary to numeric
Declare @varbinary1 as varbinary(max)
Set @varbinary1=0x08040001B471BC00

Select Convert(int,0x08) as [Precision]
Select Convert(int,0x04) as [Scale]

Select @varbinary1 as [Varbinary]
,Convert(numeric(18,4),@varbinary1) as [Varbinary to Numeric]
--RESULT

Precision
———–
8

(1 row(s) affected)

Scale
———–
4

(1 row(s) affected)

Varbinary | Varbinary to Numeric
———————– | —————————————
0x08040001B471BC00 | 1234.9876

(1 row(s) affected)

Given below is the screen image that will show how SQL keeps numeric data type in varbinary format.