Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / database / SQL-Server

(T-SQL) Converting a Variable Length Implied Decimal Float into a Decimal Float

5.00/5 (1 vote)
12 Oct 2010CPOL 16.3K  
Useful trick for handling numbers with variable length implied decimal points
I recently ran into a situation where a price data element was stored in a float field without the decimal. The field could contain values of variable decimal lengths, so the value "130445" could be "130.445" or "1.30445", depending on the value stored in another field that indicated the decimal length to the right of the decimal point.

This T-SQL code sample shows how to deal with this somewhat tricky conversion:

SQL
--Dealing with variable length implied decimal format fields
Declare @ImplDecimal int
Declare @TargetNumber float

Set @TargetNumber = 130445
Set @ImplDecimal = 3

-- Use a POWER function to divide the target number by 10 to the [Implied Decimal] Power.
Select @TargetNumber as TargetNumber, @ImplDecimal as ImpliedDecimal, @TargetNumber / POWER(10,@ImplDecimal) as Result

-- Repeat with different implied decimal precision.
Set @TargetNumber = 130445
Set @ImplDecimal = 5

Select @TargetNumber as TargetNumber, @ImplDecimal as ImpliedDecimal, @TargetNumber / POWER(10,@ImplDecimal) as Result

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)