title: Best practice for right-justifying a Numeric in TSQL
body: <p>What is the best practice for right-justifying a numeric in TSQL?</p>
  
  <p>I have to format a fixed length extract file and need to have the Numeric fields right justified. (I am using SQL Server 2005)</p>
  
  <p>I found <a href="http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=104823" rel="nofollow noreferrer">this</a>, which seems pretty straight forward.</p>
  
  <pre><code>right('            '+convert(varchar(20),a.num),12)
  </code></pre>
  
  <p>Here is the full Select statement</p>
  
  <pre><code>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)
  </code></pre>
  
  <p>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)</p>
  
  <pre><code>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))
  </code></pre>
  
  <p><strong>Updated:</strong></p>
  
  <p>Daniel Pratt's idea of using a function got me looking at <a href="http://www.sqlsharp.com/" rel="nofollow noreferrer">SQL#</a> (which we own). It has a function called <a href="http://www.sqlsharp.com/features/" rel="nofollow noreferrer">PadLeft</a>, which surprisingly enough had the same parameters and functionality as Daniel Pratt's fn_PadRight function defined in his answer below.</p>
  
  <p>Here is how to use SQL# function:</p>
  
  <pre><code>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,' ')
  </code></pre>
  
  <p>It can take both numbers and strings.</p>