Best practice for proper number alignment in TSQL

Question

What is the best practice for lawfully substantiating a number in TSQL?

I need to format a fixed-length extent file and I need the Numeric fields to be correctly justified. (I am using SQL Server 2005)

I found this one that seems pretty straight forward.

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

Here is the complete 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 ask this question because I found this line of code in a job that appears DIRECTLY complex (it also removes the decimal and zero padding)

 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, the idea of ​​using a function made me look at SQL # (which we have). It has a function called PadLeft , which, oddly enough, had the same parameters and functionality as the Daniel Pratt function fn_PadRight defined in his answer below.

Here's how to use the 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 accept both numbers and strings.

Answer 1

The only thing I can offer to help with the “crazy complexity” is to encapsulate it in one or more functions. Here is a slightly modified version of what we use:

 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 

Answer 2

Not like my answer, but it is best to do it elsewhere than SQL. SQL is for storing retrieval and processing data, not for visualization. Do not format it for display. You would be much better IMHO, having a console application that retrieves data and then generates a file.

But with this, 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) 

Hard coded spaces are so unpleasant that it will probably break for a huge number, but then again, if you are generating a fixed file, you are going to generate garbage anyway for a huge amount

Edit

Ken I read the ops post, and yes, it formats the data into a fixed-width file. The point is that you should do formatting at the application level, not in SQL. Yes, no one visually looks at the data, but I think I feel that you are still generating data, we are probably splitting the hair.

Answer 3

It would be best practice overall to have database return data and have a data presentation format. You do not have to format the data in the database.

---

Now, as the comment says, you create an extract file. I'm still wondering how you are going to get data from SQL Server and to a file. Of course, SQL Server does not create a disk file through a query?

I still recommend decoupling data from its view, even if the view is a fixed-length file. This is what we did “in the good old days,” but should be avoided today when we have enough computers to handle things like sharing problems.