2

I need to format numbers like:-

1.99
21.34
1797.94
-300.36
-21.99
-2.31

Into a format mask of 0000.00, using SQL-Server 2005 T-SQL. Preserving the signed integers and the decimals after the dot. This would be used for text file exports for a financial system. It requires it to be in this format.

e.g.-

0001.99
0021.34
1794.94
-0300.36
-0021.99
-0002.31

Previously, it was done in MS Access as Format([Total],"0000.00") but SQL-Server doesn't have this function.

Learner74
  • 143
  • 1
  • 3
  • 9

2 Answers2

4
;WITH t(c) AS
(
SELECT 1.99 UNION ALL 
SELECT 21.34  UNION ALL 
SELECT 1797.94  UNION ALL 
SELECT -300.36  UNION ALL 
SELECT -21.99  UNION ALL 
SELECT -2.31 
)
SELECT  
     CASE WHEN SIGN(c) = 1 THEN '' 
          ELSE '-' 
     END + REPLACE(STR(ABS(c), 7, 2), ' ','0') 
FROM t

Returns

0001.99
0021.34
1797.94
-0300.36
-0021.99
-0002.31
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • 1
    You always have a good CTE. I had a nice one for Oracle yesterday, recursively tracing invoices through the system (parent / child relationship) – SQLMason May 12 '11 at 14:40
  • Thanks Martin! Your solution was the one I was most looking for. It's very elegant! – Learner74 May 12 '11 at 15:02
1

You could make a function like this:

CREATE FUNCTION [dbo].padzeros 
(
    @money MONEY,
    @length INT
)
RETURNS varchar(100)
-- =============================================
-- Author:      Dan Andrews
-- Create date: 05/12/11
-- Description: Pad 0's
--
-- =============================================
-- select dbo.padzeros(-2.31,7)
BEGIN

DECLARE @strmoney VARCHAR(100), 
        @result VARCHAR(100)

SET @strmoney = CAST(ABS(@money) AS VARCHAR(100))
SET @result = REPLICATE('0',@length-LEN(@strmoney)) + @strmoney
IF @money < 0 
    SET @result = '-' + RIGHT(@result, LEN(@result)-1)

RETURN @result

END

example:

select dbo.padzeros(-2.31,7)
SQLMason
  • 3,275
  • 1
  • 30
  • 40
  • 1
    I didn't notice that you didn't want to keep the length the same (neg character takes a space). You can just concatenate '-' to the string instead of doing the RIGHT – SQLMason May 12 '11 at 14:33
  • Hi Dan, thanks for the code. Yeah, its odd that the sign takes up a space like you say, instead of being totally fixed-width. It's for an old Cobol based financial system. :-/ – Learner74 May 12 '11 at 15:01
  • I do some contract work in the evenings programming in FORTRAN on a DEC Alpha... yeah... I feel your pain. I probably could rewrite their whole system in .NET and it would run better and be more functional, but consulting FORTRAN developers make more than consulting .NET developers. :) Besides, it reminds me of the days when programming was magical. – SQLMason May 13 '11 at 12:06