[sql-server] Right pad a string with variable number of spaces

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.

Based on KMier's answer, addresses the comment that this method poses a problem when the field to be padded is not a field, but the outcome of a (possibly complicated) function; the entire function has to be repeated.

Also, this allows for padding a field to the maximum length of its contents.

WITH
cte AS (
  SELECT 'foo' AS value_to_be_padded
  UNION SELECT 'foobar'
),
cte_max AS (
  SELECT MAX(LEN(value_to_be_padded)) AS max_len
)
SELECT
  CONCAT(SPACE(max_len - LEN(value_to_be_padded)), value_to_be_padded AS left_padded,
  CONCAT(value_to_be_padded, SPACE(max_len - LEN(value_to_be_padded)) AS right_padded;

This is based on Jim's answer,

SELECT
    @field_text + SPACE(@pad_length - LEN(@field_text)) AS RightPad
   ,SPACE(@pad_length - LEN(@field_text)) + @field_text AS LeftPad

Advantages

• More Straight Forward
• Slightly Cleaner (IMO)
• Faster (Maybe?)
• Easily Modified to either double pad for displaying in non-fixed width fonts or split padding left and right to center

Disadvantages

• Doesn't handle LEN(@field_text) > @pad_length

The easiest way to right pad a string with spaces (without them being trimmed) is to simply cast the string as CHAR(length). MSSQL will sometimes trim whitespace from VARCHAR (because it is a VARiable-length data type). Since CHAR is a fixed length datatype, SQL Server will never trim the trailing spaces, and will automatically pad strings that are shorter than its length with spaces. Try the following code snippet for example.

SELECT CAST('Test' AS CHAR(20))

This returns the value 'Test '.