Right pad a string with variable number of spaces

sql-server, ssrs-2008

Solution

Whammo blammo (for leading spaces):

SELECT 
    RIGHT(space(60) + cust_name, 60),
    RIGHT(space(60) + cust_address, 60)

OR (for trailing spaces)

SELECT
    LEFT(cust_name + space(60), 60),
    LEFT(cust_address + space(60), 60),

Problem

I have a customer table that I want to use to populate a parameter box in SSRS 2008. The `cust_num` is the value and the concatenation of the `cust_name` and `cust_addr` will be the label. The required fields from the table are: ``` cust_num int PK cust_name char(50) not null cust_addr char(50) ``` The SQL is: ``` select cust_num, cust_name + isnull(cust_addr, '') address from customers ``` Which gives me this in the parameter list: ``` FIRST OUTPUT - ACTUAL 1 cust1 addr1 2 customer2 addr2 ``` Which is what I expected but I want: ``` SECOND OUTPUT - DESIRED 1 cust1 addr1 2 customer2 addr2 ``` What I have tried: ``` select cust_num, rtrim(cust_name) + space(60 - len(cust_name)) + rtrim(cust_addr) + space(60 - len(cust_addr)) customer from customers ``` Which gives me the first output. ``` select cust_num, rtrim(cust_name) + replicate(char(32), 60 - len(cust_name)) + rtrim(cust_addr) + replicate(char(32), 60 - len(cust_addr)) customer ``` Which also gives me the first output. I have also tried replacing `space()` with `char(32)` and vice versa I have tried variations of `substring`, `left`, `right` all to no avail. I have also used `ltrim` and `rtrim` in various spots. 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. 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.

Original source