title: Right pad a string with variable number of spaces
body: <p>I have a customer table that I want to use to populate a parameter box in SSRS 2008. The <code>cust_num</code> is the value and the concatenation of the <code>cust_name</code> and <code>cust_addr</code> will be the label. The required fields from the table are:</p>
  
  <pre><code>cust_num     int            PK
  cust_name    char(50)       not null
  cust_addr    char(50)
  </code></pre>
  
  <p>The SQL is:</p>
  
  <pre><code>select cust_num, cust_name + isnull(cust_addr, '') address
  from customers
  </code></pre>
  
  <p>Which gives me this in the parameter list:</p>
  
  <pre><code>FIRST OUTPUT - ACTUAL
  1       cust1              addr1
  2       customer2               addr2
  </code></pre>
  
  <p>Which is what I expected but I want:</p>
  
  <pre><code>SECOND OUTPUT - DESIRED
  1       cust1              addr1
  2       customer2          addr2
  </code></pre>
  
  <p>What I have tried:</p>
  
  <pre><code>select cust_num, rtrim(cust_name) + space(60 - len(cust_name)) +
                   rtrim(cust_addr) + space(60 - len(cust_addr)) customer
  from customers
  </code></pre>
  
  <p>Which gives me the first output.</p>
  
  <pre><code>select cust_num, rtrim(cust_name) + replicate(char(32), 60 - len(cust_name)) +
                   rtrim(cust_addr) + replicate(char(32), 60 - len(cust_addr)) customer
  </code></pre>
  
  <p>Which also gives me the first output.</p>
  
  <p>I have also tried replacing <code>space()</code> with <code>char(32)</code> and vice versa</p>
  
  <p>I have tried variations of <code>substring</code>, <code>left</code>, <code>right</code> all to no avail.</p>
  
  <p>I have also used <code>ltrim</code> and <code>rtrim</code> in various spots.</p>
  
  <p>The reason for the 60 is that I have checked the max length in both fields and it is 50 and I want some whitespace between the fields even if the field is maxed.  I am not really concerned about truncated data since the city, state, and zip are in different fields so if the end of the street address is chopped off it is ok, I guess.</p>
  
  <p>This is not a show stopper, the SSRS report is currently deployed with the first output but I would like to make it cleaner if I can.</p>