SQL Server: Remove trailing Zeros in a number

---

If you like this article, please click on “+1“ button and share with your friends.

visit www.iGnani.com

---

I faced this problem in one of my projects where I had to remove the trailing zeros from the data that I received from the database before presenting it to the user. In my case, the database was designed to store numeric value with 20 digits to the right of the decimal and most of the time it used be zeros. While I could not use the round() or any other method since I had to display the values exactly, but not if they were zeros. Consider the below example:

12345.000000000000000

Displayed: 12345

12345.0012000100000

Displayed: 12345.00120001

If you can see the above two examples, you can see that I only have to remove the trailing zeros from the digits after the decimal point. Though you can write a program or a stored procedure to do this, I did break my head on this and in the end it turned out to be pretty simple.

Select cast(cast(12345.001200010000 as DECIMAL(28,20)) as float)

12345.00120001

Since SQL Server formats float without trailing zeros, you can remove trailing zeroes by casting the value to a float.

Why am I first converting it to Decimal and then to float?

I just wanted to demonstrate that we can use this method even with varchar() data.

Cheers!!!!

Technorati Tags: SQL Server,Remove Trailing Zeros
WordPress Tags: SQL Server,Remove Trailing Zeros
Blogger Labels: SQL Server,Remove Trailing Zeros

---

visit www.iGnani.com

MicroMind Information Systems
#4013, K.R. Road, Banashankari II Stage,
BANGALORE - 560070
KARNATAKA, INDIA
Phone: +91 80 26762747

---