amitsingh308 (8/29/2013)
HelloI have to generate a report in which amount should be 13 digits.
e.g 2000- convert it into paisa 200000 then 13 digit amount is 0000000200000
but I want it to do in SQl query..
Select (amount*100) from table
then need to add 0s as per count 13-len(amount*100) before the amount
please guide me..
One of the fastest ways of doing such a thing is with a little hardcoding. You might want to avoid things like REPLICATE to calculate a certain number of concatenated zero's because its just not as fast as a string literal. You should have good luck with the following...
SELECT RIGHT('0000000000000' + CAST((Amt*100) AS VARCHAR(13)),13)
FROM dbo.SomeTable
;
Of course, such formatting should probably be done in the front-end, if there is one.
--Jeff Moden
Change is inevitable... Change for the better is not.