RPAD
The RPAD function truncates strings from the right, or pads them with spaces (or specified characters) on the right, to make all returned values the same specified length.
Syntax
RPAD (expression, padded_length [, pad_string])
Remarks
• The expression argument can be a literal expression, a variable set off by single-quotes, or a SQL expression specifying table.columnName. The data type of the column specified must be compatible with VARCHAR or a related data type, but not INTEGER, TINYINT, or CHAR(1).
• If expression is an empty string or a NULL string, RPAD returns NULL.
• The padded_length argument is an integer that specifies the length of the returned values.
• If padded_length is zero or negative, RPAD returns an empty string.
• The pad_string argument is optional. If it is omitted, spaces are used as the right-padding character; otherwise, pad_string is added repeatedly on the right until the return value reaches the specified string length, as shown in the fourth example below.
• If pad_string is an empty string or a NULL string, RPAD returns NULL.
Note: See also the related function
LPAD.
Example (Retrieve the First Character)
The following SQL select uses RPAD to retrieve just the first two characters from the values in the column FirstName.
SELECT RPAD (table.FirstName, 2) FirstInitial FROM table
Example (Truncate Values)
The following SQL select uses RPAD to truncate the values from the FamilyName column so that only the first twelve characters from very long family names are returned in the result column that has the alias LastName(12).
SELECT RPAD (table.FamilyName, 12) LastName(12) FROM table
Example (Limit Values or Right-Pad with a Value)
The following SQL select uses RPAD to limit the values of SectionTitle to the first 36 characters, and to append enough periods to shorter section titles to bring their character counts to 36.
SELECT RPAD (table.SectionTitle, 36, '.') FROM table
Example (Limit Values or Right-Pad with a Pattern of Values)
When pad_string is more than a single character, the specified characters are repeated as padding until the length specified by padded_length is reached.
SELECT RPAD (table.LastName, 10, '*...') FROM table
In this example, a LastName of “Shimabukuro” would return “Shimabuk”; a LastName of “Ho” would return “Ho*...*..” (that is, with all or part of the pattern asterisk-dot-dot-dot repeated until a count of 10 characters has been reached).