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.

Albuminoid

What font is the parameter box listed in? If it's not a fixed-width font, good luck! If you can specify a font, try Consolas, Lucida Console, or Courier New. You should also confirm in Management Studio (again using a fixed width font) that the output really is correct, just the rendering in SSRS makes it appear wrong. – Schutt

Should have been something that I considered. It doesn't look like I can change the font used through the GUI, so I need to find another way. – Albuminoid

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

Atronna

I know this answer has sat out here for years, but the trailing spaces one doesn't work for me. Seems like no matter what I do, the trailing spaces get trimmed off. – Spaceship

You are correct that the trailing spaces won't work, because the MSFT left function automatically trims off trailing spaces. Use the following trick which adds a dummy x to the end of the string before using LEFT: select left(convert(varchar(50),isnull(' abc ','')) + SPACE(11) + 'x',11)+'this shows the space working' – Rocha

What I posted works for me on SQL Server 2008 R2 and 2016 Express Edition with varchar fields, so I'd guess that something else is removing the spaces from the end. ¯\_(ツ)_/¯ – Atronna

try CAST(LEFT(cust_name+ space(60), 60) AS VARCHAR(60)) – Mok

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

Footstone

Hmmm, doesn't work in SSMS 18. Output is still trimmed. – Edytheee

Answer 3

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

Yelp

This is actually slower and more complicated when your @field_text includes converts, casts and/or functions nested. You have to repeat the entire function twice. – Incipient

Answer 4

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;

Ogbomosho

Answer 5

declare @t table(f1 varchar(50),f2 varchar(50),f3 varchar(50))
    
insert into @t values
 ('foooo','fooooooo','foo')
,('foo','fooooooo','fooo')
,('foooooooo','fooooooo','foooooo')

select 
    concat(f1
          ,space(max(len(f1)) over () - len(f1))
          ,space(3)
          ,f2
          ,space(max(len(f2)) over () - len(f2))
          ,space(3)
          ,f3
          )
from @t

result

foooo       fooooooo   foo
foo         fooooooo   fooo
foooooooo   fooooooo   foooooo

Bearskin