DB2 Version 9.7 for Linux, UNIX, and Windows

RPAD scalar function


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

The schema is SYSIBM.

The RPAD function returns a string composed of string-expression padded on the right, with pad or blanks. The RPAD 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. 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 n, where n is 32 672 if string-expression is a character string, or 16 336 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 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 code page as string-expression. The value of string-expression and the value of 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

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

Assume that NAME is a VARCHAR(15) column containing the values "Chris", "Meg", and "Jeff". The following query will completely pad out a value on the right with periods:
```
SELECT RPAD(NAME,15,'.' ) AS NAME FROM T1;
```
returns:
```
NAME
---------------
Chris..........
Meg............
Jeff...........
```
Assume that NAME is a VARCHAR(15) column containing the values "Chris", "Meg", and "Jeff". The following query will completely pad out a value on the right with pad (note that in some cases there is a partial instance of the padding specification):
```
SELECT RPAD(NAME,15,'123' ) AS NAME FROM T1;
```
returns:
```
NAME 
--------------- 
Chris1231231231 
Meg123123123123 
Jeff12312312312
```
Assume that NAME is a VARCHAR(15) column containing the values "Chris", "Meg", and "Jeff". The following query will only pad each value to a length of 5:
```
SELECT RPAD(NAME,5,'.' ) AS NAME FROM T1;
```
returns:
```
NAME 
----- 
Chris 
Meg.. 
Jeff.
```
Assume that NAME is a CHAR(15) column containing the values "Chris", "Meg", and "Jeff". Note that the result of RTRIM is a varying length string with the blanks removed:
```
SELECT RPAD(RTRIM(NAME),15,'.' ) AS NAME FROM T1;
```
returns:
```
NAME 
--------------- 
Chris.......... 
Meg............ 
Jeff...........
```
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 RPAD(NAME,4,'.' ) AS NAME FROM T1;
```
returns:
```
NAME 
---- 
Chri 
Meg. 
Jeff
```