Right pad a string with variable number of spaces

Question

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.

Answer 1

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), 

Answer 2

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 '.