asked on

How to remove right most two zeros in sql server

I am rounding a decimal digit(18,6) to (10,4).
The data in the table is like
27.427000
I have to select the above data in the following format
0000274270

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 ,
coventri

ASKER CERTIFIED SOLUTION

chapmandew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

BrandonGalderisi

This will also work.

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

David Todd

Hi,

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?

Cheers
David

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 ...

EugeneZ

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)
---result:
0000274270