LPAD
The LPAD function truncates strings from the right, or pads them with spaces (or specified characters) on the left, to make all returned values the same specified length.
Syntax
LPAD (expression, padded_length [, pad_string])
Remarks
• The expression argument can be a literal, 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, LPAD returns NULL.
• The padded_length argument is an integer that specifies the length of the returned values.
• If padded_length is zero or negative, LPAD returns an empty string.
• The pad_string argument is optional. If it is omitted, spaces are used as the left-padding character; otherwise, pad_string is added repeatedly as left-padding until the return value reaches the specified integer string length, as shown in the fourth example below.
• If pad_string is an empty string or a NULL string, LPAD returns NULL.
Note: See also the related function
RPAD.
Example (Retrieve the First Character)
The following SQL example uses LPAD to retrieve just the first character from the values in the column FirstName.
SELECT LPAD (table.FirstName, 1) FirstInitial FROM table
Example (Truncate Values)
The following SQL example uses LPAD 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 set column that has the alias LastName(12).
SELECT LPAD (table.FamilyName, 12) LastName(12) FROM table
Example (Limit Values or Left-Pad with a Value)
The following SQL example uses LPAD to limit the values of SectionTitle to the first 36 characters, and to precede section titles of fewer than 36 characters with enough periods to bring their character counts to 36.
SELECT LPAD (table.SectionTitle, 36, '.') FROM table
Example (Limit Values or Left-Pad with a Pattern of Values)
When pad_string is more than a single character, the specified character pattern (or beginning of the pattern) is repeated as padding until the exact string length is reached.
SELECT LPAD (table.LastName, 8, '*...') FROM table
In this example, a last name of “Shimabukuro” would return “Shimabuk” and a last name of “Ho” would return “*...*.Ho”.