title: SQL Server Padding 0's with RIGHT Not Working
body: <p>I am using SQL Server 2008.</p>
  
  <p>I have a varchar(30) column which holds check numbers.  If the payment was made using a credit card, checknbr will be a zero-length string.  I need to pad this data with leading zeroes such that the entire output is ten digits long.  If there is no check number, it should just be ten zeroes.  </p>
  
  <p>Here is the data I would like to see:</p>
  
  <pre><code>    0000000000
      0000000000
      0000000114
      0000000105
      0000000007
  </code></pre>
  
  <p>Here is the query I have been using and am stuck on:</p>
  
  <pre><code>    SELECT RIGHT((CASE  
          WHEN LEN(checknbr) = 0 THEN '0000000000'
          ELSE '0000000000'+checknbr
      END),10) as checknbr
      FROM payhistory
      WHERE number = 12345678
  </code></pre>
  
  <p>And this is what I get:</p>
  
  <pre><code>    0000000000
      0000000000
      114
      105
      7
  </code></pre>
  
  <p>Trying another way:</p>
  
  <pre><code>    SELECT RIGHT('0000000000'+checknbr,10)
      FROM payhistory
      WHERE number = 3861821
  </code></pre>
  
  <p>And I get the same results.  Oddly enough, I have a varchar(10) column where </p>
  
  <pre><code>    RIGHT('0000'+EDC.DatabaseNumber,4) 
  </code></pre>
  
  <p>yields exactly the results I am after.  What am I missing here? Something about a quirk with the RIGHT function?  </p>
  
  <p>Thanks in advance</p>