IBM Support

LPAD & RPAD Scalar Functions

News


Abstract

LPAD & RPAD Scalar Functions

Content


Db2 for i support for built-in functions is extended to include padding support:

  • LPAD - The LPAD function returns a string composed of expression that is padded on the left.
  • RPAD - The RPAD function returns a string composed of expression that is padded on the right.

Consult the SQL reference for complete details:  LPAD scalar function, RPAD scalar function



LPAD & RPAD
LPAD is used to pad the left side of a base string with a given pad string. It will repeat the pad string until the given length is met. RPAD is similar but adds the padding on the right side of the base string. This can be useful anywhere a certain pad is required, such as requiring leading 0's or leading spaces.

LPAD and RPAD both accept nearly any data type and CCSID, which includes date/time and numeric data types..

Examples:
LPAD(Last4OfCreditCardNumber, CreditCardLength, 'X') → 'XXXXXXXXXXXXX1234'
Where the length is variable based on the type of card that it is.

RPAD(CustFirstName,ColumnSize, ' ') → 'Matt                '
This would leave all first names with a uniform length.

 

image-20200115130745-1

[{"Business Unit":{"code":"BU054","label":"Systems w\/TPS"},"Product":{"code":"SWG60","label":"IBM i"},"Component":"","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"All Versions","Edition":"","Line of Business":{"code":"LOB08","label":"Cognitive Systems"}}]

Document Information

Modified date:
15 January 2020

UID

ibm11164724