Padding Values in SQL

[ousoonerjoe]

What is the best way to pad a field (Left or Right) in SQL?

Flat file outputs are often fixed width and in some cases require very little adjusting of the data. If it can be generated via Stored Procedure in DTS, then that makes my life all that much better. Problem is the formatting.

For an 11 digit Money field I have (could be used for strings, too):

	[Amount] =CASE WHEN td.Amount > 0 THEN 
					RIGHT(REPLACE('00000000000' + CAST(td.Amount AS VARCHAR(11)),'.',''),11)
				ELSE
					'-' + REPLACE(RIGHT(REPLACE('00000000000' + CAST(td.Amount AS VARCHAR(11)),'.',''),10),'-','0')
				END

But as you can see this is very limited and clumsy. I was hoping to find something like VB's Format function, but SQL's FORMAT isn't exactly flexible.

I found a lot of topics that pertained to formatting, but nothing about padding for a fixed width file output. I welcome any suggestions you may have.

Thank you.

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

 

[TysonLPrice]

I've never used this function (it's from a site that provides a list of the I copied) but it reads like what you want. You can probably convert it from a function.

-- =================================================
-- LJust -Left justify string Function
-- =================================================
-- Returns a copy of @String Left justified in a
-- string of length width. Padding is done using the
-- specified fillchar string(default is a space). The
-- original string is returned if width is less than
-- len(s).

IF OBJECT_ID(N'LJust') IS NOT NULL
   DROP FUNCTION LJust
GO
CREATE FUNCTION LJust
   (
    @String VARCHAR(MAX),
    @width INT,
    @fillchar VARCHAR(10) = ' '
   )
/*
e.g.

select dbo.LJust('Help me please',5,'*-')
select dbo.LJust('error',100,'*!=')
select dbo.LJust('error',null,null)
select dbo.LJust(null,default,default)

*/
RETURNS VARCHAR(MAX)
AS BEGIN
      IF @string IS NULL
         RETURN NULL
      DECLARE @LenString INT
      DECLARE @LenFiller INT
-- Declare the return variable here
      SELECT   @lenString = LEN(REPLACE(@String, ' ', '|')),
               @Fillchar = COALESCE(@Fillchar, ' '),
               @LenFiller = LEN(REPLACE(@Fillchar, ' ', '|')),
               @width = COALESCE(@Width, LEN(@String) * 2)
      IF @Width < @lenString
         RETURN @String
      RETURN STUFF(LEFT(
                       REPLICATE(@Fillchar, (@width / @LenFiller) + 1),
                       @width),
                    1, @LenString, @String)  
   END
GO