Removing trailing zeros from decimal numbers in SQL
Say you've got a numeric field (SQL Server DB) which returns numbers to three decimal places, e.g. 1.000, 2.125, 3.250, etc.
How do you return those numbers minus the trailing zeros? I can't believe this is so difficult. Surely there's an easy way to do it. Here's the hack I used:
SELECT replace(rtrim(replace(replace(rtrim(replace(myCol,'0',' ')),' ','0'),'.',' ')),' ','.') as myColDisplay
FROM myTable
FROM myTable
Ick.
Comments (Comment Moderation is enabled. Your comment will not appear until approved.)
[Add Comment]
Awesome piece of art mate :)
# Posted By Andrea
| 7/16/08 6:32 AM
Great! This helped me solve my problem.
# Posted By Narendra
| 4/1/10 5:53 AM
Fantasic piece of code. Does exactly what I need. Thanks so much.
# Posted By Patricia
| 4/21/10 7:52 AM
Thanks for this, I also could not believe how hard it is to solve this problem. Thumbs up :)
# Posted By Jas
| 10/3/11 10:32 AM
[Add Comment]