Click here to Skip to main content
15,887,683 members
Articles / Database Development / SQL Server
Tip/Trick

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

Rate me:
Please Sign up or sign in to vote.
5.00/5 (1 vote)
12 Oct 2010CPOL 16.1K   4   1
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)


Written By
United States United States
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
Generalgood artichle Pin
shakesperoo3@hotmail.com14-Oct-10 3:54
shakesperoo3@hotmail.com14-Oct-10 3:54 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.