Store Decimal values without Trailing Zeros

Question

Store Decimal values without Trailing Zeros

IT researcher

SSCertifiable

Points: 7567
More actions
December 14, 2020 at 12:46 pm

#3820247
I am using SQL Server 2008 R2 version.
Is there any method to store decimal value in SQL without extra trailing zeros(like Float data type format).
I have created a column with decimal data type with highest precision.
Actual values:
```
2.50000000
4.55530000
1.85333310
```
Expected:
```
2.5
4.5553
1.8533331
```
Is it possible to store decimal values in SQL like this?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 1

No, and your question is based on a fundamental misconception about numbers in computers. You are confusing what is stored with how it is presented.

Numbers in a computers are stored as numbers. They are not stored as strings. I don't know the exact format for decimal data, but basically, a value 4.5553 of the type decimal(20,8) is stored with an integer portion and a decimal portion. Since the number of decimals is fixed, the decimal portion in this case is 55530000 and that how it stored. That is not the string '55530000', but the number 55530000.

How it is presented is another matter. In SSMS decimal values are displayed with trailing zeroes, but an application could use a different means of formatting and strip the trailing zeroes. Or decide that the user will never be able to cope with more than two decimals and just round the values.

For float values on the other hand, SSMS uses a formatting library which strips trailing zeroes, or rather what seem to be trailing zeroes. SQLCMD uses a different library, and the same float value may display differently in SSMS and SQLCMD.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Michael L John One Orange Chip Points: 27038 More actions · Answer 2

IT researcher wrote:

I am using SQL Server 2008 R2 version. Is there any method to store decimal value in SQL without extra trailing zeros(like Float data type format).

Are you using a float data type, or a decimal data type? If it's a float, then that is most likely the wrong data type to use.

This reply was modified 3 years, 4 months ago by Michael L John.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 3

IT researcher wrote:

I am using SQL Server 2008 R2 version. Is there any method to store decimal value in SQL without extra trailing zeros(like Float data type format). I have created a column with decimal data type with highest precision.
Actual values:
2.50000000
4.55530000
1.85333310
Expected:
2.5
4.5553
1.8533331
Is it possible to store decimal values in SQL like this?

What is the datatype of the column you have the numbers stored in?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)