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

Solution 1

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

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

Comments

[craphunter]

Thanks for your reply. I have to format the data in the database.

[Ken White]

Although that was my first thought from the post subject, he specifically said he was producing a fixed length extract file.

[Ken White]

@Josh: Read the post before answering. There is no "visualization" or display here - the OP is producing an extract file. Please explain how a text file does presentation or formatting of data.

[dburges]

I doubt the file is being created through the application, most of these things ar done through jobs or SSIS pacakages or the like and never even get close to the application.