Brian's Blog

items I see across my tribes

SQL: Implied Decimal

November 17
by briancarter 17. November 2009 08:29

It is common, especially in IBM environments, to have implied decimals.  To export from a text (nvarchar) column with a decimal to a field with an implied decimal, the first step is to cast to a numeric.


Decimal and numeric are equivalent.  Below is the tsql format:  

 decimal[(p[, s])] and numeric[(p[, s])]

p (precision)

Specifies the maximum total number of decimal digits that can be stored, both to the left and to the right of the decimal point. The precision must be a value from 1 through the maximum precision. The maximum precision is 38. The default precision is 18.

s (scale)

Specifies the maximum number of decimal digits that can be stored to the right of the decimal point. Scale must be a value from 0 through p. Scale can be specified only if precision is specified. The default scale is 0; therefore, 0 <= s <= p. Maximum storage sizes vary, based on the precision.

First the text is cast to a numeric value with 2 digits to the right; then it is multiplied by 100 to shift the value removing the decimal.  If you have more digits to the right of the decimal, you would update the 2 and the 100.  The final cast to an integer is necessary to remove the 2 digits now filled with 0. 

select TID, AValue, 
right('0000000000'+ CAST(CAST(AValue as numeric(12,2)) * 100 as int), 10) AS CValue
from Test01

image

You can use this technique in a View, Stored Procedure, or any TSql commands.

Categories: Development


 Questions or Feedback, my contact information is located on my About page.


The opinions, thoughts, and comments made in these blog posts are solely my own (unless otherwise stated). They do not reflect the opinions, thoughts or practices of my employer, my universities, my family, or anyone else. Also, I retain the right to change my mind about anything I publish here without having to go back and edit posts that occurred in the past. 

These are my opinions, or just as likely, someone else's opinions that I leveraged for my own.