LPAD scalar function

The LPAD function pads a string on the left with a specified character string or with blanks.

The schema is SYSIBM.

The LPAD function treats leading or trailing blanks in the 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 built-in character string, graphic string, binary string, CLOB or DBCLOB value, numeric value, or datetime value. If the value is:

A CLOB, numeric, or datetime value, it is implicitly cast to VARCHAR before the function is evaluated
A DBCLOB value, it is implicitly cast to VARGRAPHIC before the function is evaluated

The data type of the value cannot be a BLOB (SQLSTATE 42815).

integer

An expression that specifies the actual length of the result in the string units of the string expression. The expression must return a built-in numeric valueor character string. In a Unicode database, the expression can also return a graphic string, in which case it is first converted to a character string before the function is evaluated. If the value returned by the expression is not an integer, it is cast to INTEGER before the function is evaluated. 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 units of the string expression.

pad

An expression that specifies the string with which to pad. The expression must return a built-in character string, graphic string, binary string, CLOB or DBCLOB value, numeric value, or datetime value. If the value is:

A CLOB, numeric, or datetime value, it is implicitly cast to VARCHAR before the function is evaluated
A DBCLOB value, it is implicitly cast to VARGRAPHIC before the function is evaluated

The data type of pad cannot be a BLOB (SQLSTATE 42815).

The data type of the string expression determines the default pad string:

The SBCS blank character, if the string expression is a character string.
The ideographic blank character, if the 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.
Hexadecimal zero (X'00'), if the string expression is a binary string.

Result

The data type of the result depends on the data type of the string expression:

VARCHAR if the data type is VARCHAR or CHAR
VARGRAPHIC if the data type is VARGRAPHIC or GRAPHIC
VARBINARY if the data type is VARBINARY or BINARY

The result of the function is a varying length string that has the same string unit and code page as the string expression. The values for the string expression and the pad expression must have compatible data types. If the string expression and pad expression have different code pages, then the pad expression is converted to the code page of the string expression. If either the string expression or the pad expression is FOR BIT DATA or a binary string, 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), VARCHAR(n), BINARY(n), or VARBINARY(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
```