Best practice for right-justifying a Numeric in TSQL

Question

What is the best practice for right-justifying a numeric in TSQL?

I have to format a fixed length extract file and need to have the Numeric fields right justified. (I am using SQL Server 2005)

I found this, which seems pretty straight forward.

right('            '+convert(varchar(20),a.num),12)

Here is the full Select statement

select
    a.num,
    fixed_number =
     right('            '+convert(varchar(20),a.num),12)
from
    (
    --Test Data
    select num = 2400.00 union all
    select num = 385.00 union all
    select num = 123454.34
    ) a

Results:

num        fixed_number 
---------- ------------ 
2400.00         2400.00
385.00           385.00
123454.34     123454.34

(3 row(s) affected)

I am asking this question because I found this line of code at work, which appears INSANELY complex (It is also removing the decimal and zero filling)

CAST(REPLACE(REPLICATE('0', 12 - LEN(REPLACE(CAST(CONVERT(DECIMAL(10,2),@DD8DBAMT) AS VARCHAR),'.',''))) 
+ CAST(CONVERT(DECIMAL(10,2),@DD8DBAMT) AS VARCHAR),'.','') AS VARCHAR(12))

Updated:

Daniel Pratt's idea of using a function got me looking at SQL# (which we own). It has a funtion called PadLeft, which surprisingly enough had the same parameters and functionality as Daniel Pratt's fn_PadRight function defined in his answer below.

Here is how to use SQL# function:

DECLARE @F6D2 AS DECIMAL(8,2)
SET @F6D2 = 0
SQL#.String_PadLeft(@F6D2,9,' ')
SQL#.String_PadLeft(123.400,9,' ')
SQL#.String_PadLeft('abc',9,' ')

It can take both numbers and strings.

Answer 1

The best practice in general would be to have the database return data, and to have the presentation layer format the data. You shouldn't be formatting data in the database.

---

I now what the comment points out - you're creating an extract file. I still wonder how you're going to get the data out of SQL Server and into the file. Surely SQL Server isn't creating the disk file through a query?

I still recommend separating the data from its presentation, even if that presentation is a fixed-length file. This is thie kind of thing we used to do "in the good old days", but should avoid today, now that we have computers fast enough to deal with things like separation of concerns.

Answer 2

Your not going like my answer, but the best practice is to do this someplace other then SQL. SQL is meant to store retrieve and process data not visualize it. Not format it for display. You'd be much better IMHO having a console application that pulls the data then generates the file.

But with that said when I did this before, I did it like this:

declare @num int
set @num=1555
select replicate(' ',20-len(cast(@num as varchar))) + cast(@num as varchar)

Hardcoding spaces so nasty, this will probally break for a huge number but then again if your generating a fixed file your going generate garbage anyways for a huge number

Edit

Ken I did read the ops post, and yes he is formatting the data into a fixed width file. The point is that you should do formatting in an application tier, not in SQL. Yes there is no one visually looking at the data, but I guess I feel like you are still formating the data, we are probally splitting hairs.

Answer 3

The only thing I can suggest to help with the "insane complexity" is to encapsulate it in one or more functions. Here's a somewhat modified version of something we're using:

CREATE FUNCTION [dbo].[fn_PadRight]
(
 @Value nvarchar(4000)
 ,@NewLength int
 ,@PadChar nchar(1) = ' '
) RETURNS nvarchar(4000)
AS
BEGIN
 DECLARE @ValueLength int
 SET @ValueLength = LEN(@Value)

 IF (@NewLength > @ValueLength) BEGIN
  SET @Value = @Value + REPLICATE(@PadChar, @NewLength - @ValueLength)
 END

 RETURN @Value
END
GO

CREATE FUNCTION [dbo].[fn_FormatAmountDE]
(
 @Value money
) RETURNS nvarchar(4000)
AS
BEGIN
 RETURN [dbo].[fn_PadRight](REPLACE(CAST(@Value AS varchar), '.', ''), 12, '0')
END
GO