Cast and Convert decimal to a zero in left in T-SQL

Question

Let's say I have this SELECT statement

SELECT TOP 1 CAST(G.ACCOUNTINGCURRENCYAMOUNT AS decimal(15,2) )
FROM MicrosoftDynamicsAx.dbo.GENERALJOURNALACCOUNTENTRY G

And i get the result : -372263.29.So i want to get -00000000372263.29

How can i padding zero in the left ? Thank in advance

`SELECT RIGHT('000000000000000000000'+cast (@Var AS NVARCHAR(100)),15)` — Royi Namir, Feb 23 '16 at 11:21
http://stackoverflow.com/questions/16760900/pad-a-string-with-leading-zeros-so-its-3-characters-long-in-sql-server-2008 — , Feb 23 '16 at 11:24
Search on "Leading zeros SQL" and you get dozens of results/answers — PSVSupporter, Feb 23 '16 at 11:37

JCollerton · Answer 1 · 2016-02-23T12:32:21.387

1

You won't be able to store a numeric with the leading zeros, you would need to store it as a varchar. So when you want to have leading zeros, you can make a choice between:

Storing values in the table as a numeric, then selecting the result as a varchar in order to display the leading zeros.
Storing in the table as a varchar and then converting to a numeric whenever you want to do calculations.

In the above it looks like you have a numeric value in the table and want to pull out a fixed length string. Try:

SELECT TOP 1
     CASE 
         WHEN CAST(G.ACCOUNTINGCURRENCYAMOUNT AS decimal(15,2) >= 0 THEN
             RIGHT('000000000000000000000' + CAST(CAST(G.ACCOUNTINGCURRENCYAMOUNT AS decimal(15,2)) AS VARCHAR), 15)
         ELSE 
             '-' + RIGHT('000000000000000000000' + CAST(-1 * CAST(G.ACCOUNTINGCURRENCYAMOUNT AS decimal(15,2)) AS VARCHAR), 15)
    END
FROM 
     MicrosoftDynamicsAx.dbo.GENERALJOURNALACCOUNTENTRY G

edited Feb 23 '16 at 12:32

answered Feb 23 '16 at 11:45

JCollerton

3,227
2
20
25

Thank you for reply, also i get `00000-372263.29` i check way the second condition don't work – stoner Feb 23 '16 at 12:13
Yes now i get 000000372263.29 but i want -000000372263.29 i don't want change the default vaule – stoner Feb 23 '16 at 12:26
Finally got it, try running that now? The other answer won't get you the right thing I don't think as it doesn't account for negative numbers. – JCollerton Feb 23 '16 at 12:32

Gordon Linoff · Accepted Answer · 2016-02-24T02:04:27.767

The key idea is that the output needs to be converted to a string to put 0s in front. If you don't like typing out long strings of 0s, you can use str() and replace():

SELECT TOP 1 REPLACE(STR(G.ACCOUNTINGCURRENCYAMOUNT, 15, 2), ' ', '0')
FROM MicrosoftDynamicsAx.dbo.GENERALJOURNALACCOUNTENTRY G;

EDIT:

To handle negative numbers . . .

SELECT TOP 1 (CASE WHEN G.ACCOUNTINGCURRENCYAMOUNT >= 0
                   THEN REPLACE(STR(G.ACCOUNTINGCURRENCYAMOUNT, 15, 2), ' ', '0')
                   THEN '-' + REPLACE(STR(ABS(G.ACCOUNTINGCURRENCYAMOUNT), 14, 2), ' ', '0')
              END)
FROM MicrosoftDynamicsAx.dbo.GENERALJOURNALACCOUNTENTRY G;

I like this one , in the result i have `00000-372263.29` but i should edit this to get `-00000372263.20` — stoner, Feb 23 '16 at 12:08

Cast and Convert decimal to a zero in left in T-SQL

2 Answers2