DB2 Scalar functions - RPAD - www.cadcobol.com.br

RPAD

The RPAD function returns a string composed of string-expression padded on the right, with pad or blanks.

The schema is SYSIBM.

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 character string, graphic string, BINARY, VARBINARY, numeric, or datetime data type.
CLOB and DBCLOB are supported through implicit casting.
If the value is a CLOB, it is implicitly cast to VARCHAR before the function is evaluated.
If the value is a DBCLOB, it is implicitly cast to VARGRAPHIC before the function is evaluated.
If the data type of the string-expression value is numeric or datetime, the value is implicitly cast to VARCHAR before the function is evaluated.
The data type of string-expression cannot be a BLOB (SQLSTATE 42815).

integer

An expression that specifies the actual length of the result in the string units of string-expression.
The expression must return a value that is a built-in numeric, CHAR, or VARCHAR data type.
In a Unicode database, the expression can also be a GRAPHIC or VARGRAPHIC data type.
CHAR, VARCHAR, GRAPHIC, and VARGRAPHIC are supported through implicit casting.
If the expression is not of type 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 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 character string, graphic string, BINARY, VARBINARY, numeric, or datetime data type.
CLOB and DBCLOB are supported through implicit casting.
If the value is a CLOB, it is implicitly cast to VARCHAR before the function is evaluated.
If the value is a DBCLOB, it is implicitly cast to VARGRAPHIC before the function is evaluated.
If the data type of the pad value is numeric or datetime, the value is implicitly cast to VARCHAR before the function is evaluated.
The data type of pad cannot be a BLOB (SQLSTATE 42815).

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.
Hexadecimal zero (X'00'), if string-expression is a binary string.

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 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 or binary, 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), 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

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
```