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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to sql-server

Examples related to ssrs-2008