LPAD scalar function

The LPAD function returns a string that is composed of string-expression that is padded on the left, with pad or blanks. The LPAD function treats leading or trailing blanks in string-expression as significant.

Padding occurs only if the actual length of string-expression is less than integer, and if pad is not an empty string.

The schema is SYSIBM.

string-expression

An expression that specifies the source string. The expression must return a value that is a built-in string data type that is not a LOB.

integer

An integer constant that specifies the length of the result. The value must be zero or a positive integer that is less than or equal to n, where n is 32704 if string-expression is a character or binary string, or where n is 16352 if string-expression is a graphic string.

pad

An expression that specifies the string with which to pad. The expression must return a value that is a built-in string data type that is not a LOB. If pad is not specified, the pad character is determined as follows:

SBCS blank character if string-expression is a character string.
DBCS blank character if string-expression is a graphic string.
Hexadecimal zero (X'00'), if string-expression is a binary string.

The result of the function is a varying length string that has the same CCSID of string-expression. string-expression and pad must have compatible data types. If the string expressions have different CCSID sets, then pad is converted to the CCSID set of string-expression. If either string-expression or pad is FOR BIT DATA, no character conversion occurs.

The length attribute of the result depends on integer. If integer is greater than 0, the length attribute of the result is integer. If integer is 0, the length attribute of the result is 1.

The actual length of the result is determined from integer. If integer is 0, the actual length is 0, and the result is the empty result string. If integer is less than the actual length of string-expression, the actual length is integer and the result is truncated.

The result can be null; if any argument is null, the result is the null value.

Example 1: Assume that NAME is a VARCHAR(15) column that contains the values 'Chris', 'Meg', and 'Jeff'. The following query will pad a value on the left with periods.

  SELECT LPAD(NAME,15,'.' ) AS NAME 
    FROM T1;

The results are similar to the following output:

  NAME
  ---------------

  ..........Chris
  ............Meg
  ...........Jeff

Example 2: Similar to Example 1, the following query will only pad each value to a length of 5:

  SELECT LPAD(NAME,5,'.' ) AS NAME 
    FROM T1;

The results are similar to the following output:

  NAME
  ---------------

  Chris
  ..Meg
  .Jeff

Example 3: Assume that NAME is a CHAR(15) column containing the values 'Chris', 'Meg', and 'Jeff. 'Note that the LPAD function does not pad because NAME is a fixed length character field and is blank padded already. However, since the length of the result is 5, the columns are truncated:

  SELECT LPAD(NAME,5,'.' ) AS NAME 
    FROM T1;

The results are similar to the following output:

  NAME
  ---------------

  Chris
  Meg
  Jeff

Example 4: Assume that NAME is a VARCHAR(15) column containing the values 'Chris', 'Meg', and 'Jeff'. Note that in some cases, a partial instance of the pad specification is returned.

  SELECT LPAD(NAME,15,'123') AS NAME
    FROM T1

The results are similar to the following output:

NAME
---------------
1231231231Chris
123123123123Meg
12312312312Jeff