Learn how to a build a cloud-first strategyRegister Now


How to remove right most two zeros in sql server

Posted on 2009-02-24
Medium Priority
Last Modified: 2012-05-06
I am rounding a decimal digit(18,6) to (10,4).
The data in the table is like
I have to select the above data in the following format

1) toal length of record should be 10
2) I have to get only '4' digits after decimal point(after rounding)
3) I have to add leading zeros if length is less than 10

I have written the following sql

select RIGHT('0000000000' + replace(round(27.427000,4),'.',''), 10)

can any one help me

Thanks ,
Question by:coventri
LVL 60

Accepted Solution

chapmandew earned 2000 total points
ID: 23724478
this should do it:

declare @x decimal(18,6)
SET @X = 27.427000
select right('0000000000000000' + replace(cast(cast(@x as decimal(10,4)) as varchar(10)), '.', ''), 10)
LVL 39

Expert Comment

ID: 23724532
This will also work.

declare @x decimal(18,6)
SET @X = 27.427000
select right('0000000000000000' + ltrim(str(@x*10000)), 10)
LVL 35

Expert Comment

by:David Todd
ID: 23724728

This is the sort of thing that is more easily handled in the presentation layer, rather than in SQL. Most output fields have formats that are pretty flexible. What are you using for presentation? Excel? Crystal Reports? SQL Reports? Other?


PS I've no doubt that the previous suggestions will work and work well. I'm just asking if this is the best place to do this formatting ...
LVL 43

Expert Comment

by:Eugene Z
ID: 23724968
it is working too: http://msdn.microsoft.com/en-us/library/aa226054(SQL.80).aspx

declare @x decimal(18,6)
SET @X = 27.427000
select RIGHT('0000000000' + replace(cast(@x as dec(10,4)),'.',''), 10)
select RIGHT('0000000000' + replace(cast(27.427000 as dec(10,4)),'.',''), 10)

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question