title: Trying to pad spaces on the right side of a string in SQL server and it's not working
body: <p>I'm trying to create a csv file that is a set length, and therefore, the fields are a specific length. I'm trying the get my sql statement to pad these columns for me. </p>
  
  <p>I've tried substring, left, space(xx), right, reverse, converting 'TEST' to a char value and I can't get this to work. I tried reverse knowing that using right (space(20) + 'TEST') got what I wanted but obviously was padded on the wrong side.</p>
  
  <p>It seems that SQL server is just dropping the padded spaces on the right side of my string. The two below won't work. The length always comes back @ 4.</p>
  
  <pre><code>select len(reverse(right(space(20) + reverse('TEST'), 20)))
  
  select len(left('TEST' + space(20), 20))
  </code></pre>
  
  <p>This one does work. But again, the spaces are on the wrong side.</p>
  
  <pre><code>select len(right(space(20) + 'TEST', 20))
  </code></pre>
  
  <p>This works too, and returns "TEST        1"</p>
  
  <pre><code>select len(left('TEST' + '        1', 20))
  </code></pre>
  
  <p>Again, it seems that as long as there is some real text on the right side of the string, it's fine.</p>
  
  <p>Anyone have some ideas about how to make this work?</p>