DB2 10.5 for Linux, UNIX, and Windows

LPAD scalar function

The LPAD function returns a string composed of string-expression that is padded on the left, with pad or blanks.


>>-LPAD--(--string-expression--,--integer--+-------+--)--------><
                                           '-, pad-'

The schema is SYSIBM.

The LPAD function treats leading or trailing blanks in string-expression as significant. Padding will only occur if the actual length of string-expression is less than integer, and pad is not an empty string.

string-expression

An expression that specifies the source string. The expression must return a value that is a built-in CHAR, VARCHAR, GRAPHIC, VARGRAPHIC, numeric, or datetime data type. If the value is not a CHAR, VARCHAR, GRAPHIC, or VARGRAPHIC data type, it is implicitly cast to VARCHAR before evaluating the function.

integer

An integer expression that specifies the length of the result in the string units of string-expression. The expression must return a value that is a built-in numeric, CHAR, VARCHAR, GRAPHIC, or VARGRAPHIC data type. If the value is not of type INTEGER, it is implicitly cast to INTEGER before evaluating the function. The value must be zero or a positive integer that is less than or equal to the maximum length for the result data type in the string units of string-expression.

pad

An expression that specifies the string with which to pad. The expression must return a value that is a built-in CHAR, VARCHAR, GRAPHIC, VARGRAPHIC, numeric, or datetime data type. If the value is not a CHAR, VARCHAR, GRAPHIC, or VARGRAPHIC data type, it is implicitly cast to VARCHAR before evaluating the function.

If pad is not specified, the pad character is determined as follows:

SBCS blank character if string-expression is a character string.
Ideographic blank character, if string-expression is a graphic string. For graphic string in an EUC database, X'3000' is used. For graphic string in a Unicode database, X'0020' is used.

The result of the function is a varying length string that has the same string unit and code page as string-expression. The value for string-expression and the value for pad must have compatible data types. If the string-expression and pad have different code pages, then pad is converted to the code page 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 whether the value for integer is available when the SQL statement containing the function invocation is compiled (for example, if it is specified as a constant or a constant expression) or available only when the function is executed (for example, if it is specified as the result of invoking a function). When the value is available when the SQL statement containing the function invocation is compiled, if integer is greater than zero, the length attribute of the result is integer. If integer is 0, the length attribute of the result is 1. When the value is available only when the function is executed, the length attribute of the result is determined according to the following table:

Table 1. Determining the result length when integer is available only when the function is executed
Data type of string-expression	Result data type length
CHAR(n) or VARCHAR(n)	Minimum of n+100 and 32 672
GRAPHIC(n) or VARGRAPHIC(n)	Minimum of n+100 and 16 336
CHAR(n) or VARCHAR(n) or GRAPHIC(n) or VARGRAPHIC(n) with string units of CODEUNITS32 (Unicode database only)	Minimum of n+100 and 8 168

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.

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

Examples

Example 1: Assume that NAME is a VARCHAR(15) column that contains the values "Chris", "Meg", and "Jeff". The following query will completely pad out a value on the left with periods:
```
SELECT LPAD(NAME,15,'.' ) AS NAME FROM T1;
```
returns:
```
NAME 
--------------- 
..........Chris
............Meg 
...........Jeff
```
Example 2: Assume that NAME is a VARCHAR(15) column that contains the values "Chris", "Meg", and "Jeff". The following query will only pad each value to a length of 5:
```
SELECT LPAD(NAME,5,'.' ) AS NAME FROM T1;
```
returns:
```
NAME 
----- 
Chris 
..Meg 
.Jeff
```
Example 3: Assume that NAME is a CHAR(15) column containing the values "Chris", "Meg", and "Jeff". 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;
```
returns:
```
NAME 
----- 
Chris 
Meg 
Jeff
```
Example 4: Assume that NAME is a VARCHAR(15) column containing the values "Chris", "Meg", and "Jeff". In some cases, a partial instance of the pad specification is returned:
```
SELECT LPAD(NAME,15,'123' ) AS NAME FROM T1;
```
returns:
```
NAME 
--------------- 
1231231231Chris 
123123123123Meg 
12312312312Jeff
```
Example 5: Assume that NAME is a VARCHAR(15) column containing the values "Chris", "Meg", and "Jeff". Note that "Chris" is truncated, "Meg" is padded, and "Jeff" is unchanged:
```
SELECT LPAD(NAME,4,'.' ) AS NAME FROM T1;
```
returns:
```
NAME 
---- 
Chri 
.Meg 
Jeff
```