8.5.6 RPAD
Pad the end (right side) of the target data with the padding character string up to the specified number of characters.
- Organization of this subsection
(1) Specification format
scalar-function-RPAD ::= RPAD(target-data,num-chars[,padding-character-string]) target-data ::= value-expression num-chars ::= value-expression padding-character-string ::= value-expression
(2) Explanation of specification format
- target-data:
-
Specifies the character string data to be padded.
The following rules apply:
-
Specify the target data in the form of a value expression. For details about value expressions, see 7.20 Value expression.
-
Specify CHAR or VARCHAR type data for the target data.
-
You cannot specify a dynamic parameter by itself for the target data.
-
- num-chars:
-
Specifies the number of characters in the result character string after it is padded.
The following rules apply:
-
Specify the number of characters in the form of a value expression. For details about value expressions, see 7.20 Value expression.
-
Specify INTEGER or SMALLINT type data for the number of characters.
-
If a dynamic parameter is specified as the number of characters, the assumed data type of the dynamic parameter will be INTEGER type.
-
- padding-character-string:
-
Specifies the character string to be used for padding the end (right side) of the target data.
The following rules apply:
-
Specify padding-character-string in the form of a value expression. For details about value expressions, see 7.20 Value expression.
-
You must specify CHAR or VARCHAR type data for padding-character-string.
-
If padding-character-string is omitted, its assumed value is a single-byte space character.
-
If a dynamic parameter is specified by itself for padding-character-string, the assumed data type of the dynamic parameter is VARCHAR(32000).
-
The following example illustrates the result of executing the scalar function RPAD.
- Example
-
Pad the end (right side) of the data in column C1 with the character string 'xyz' repeatedly until it reaches 10 characters total.
RPAD("C1",10,'xyz') → 'ABCxyzxyzx'
Column C1 has type VARCHAR(20) and holds the character string 'ABC'.
(3) Rules
-
The data type and data length of the execution result are shown in the following table.
Table 8‒16: Data type and data length of the execution result of the scalar function RPAD Data type and data length of the target data
Data type and data length of the execution result
CHAR(n)
VARCHAR(n)
VARCHAR(n)
- Legend:
-
n: Maximum length of the target data
-
The NOT NULL constraint does not apply to the value of the execution result (the null value is allowed).
-
If either of the following conditions are met, the execution result will be a null value:
-
If the target data, number of characters, or padding character string is the null value
-
If you specify a negative value for the number of characters
-
-
If the actual length of the padding character string is 0 bytes or 0 characters, no character string padding will be performed.
-
If the number of characters in the target-data character string is greater than the value of num-chars, the function returns the specified number of characters from the beginning of the target-data character string.
Example: RPAD('ABCDE',3,'xy') → 'ABC'
-
If a character string of the specified number of characters cannot be represented in the data length of the execution result, the padding characters will be truncated in mid-string. This means that the number of characters in the execution result might be different from the specified number of characters. If you want to obtain a character string with the specified number of characters, use the scalar function CAST to change the data length of the target data.
- Examples
-
These examples assume that Unicode (UTF-8) is being used as the character encoding, and that the value and data type of column C1 are as follows:
• Value of column C1:
• Data type of column C1: VARCHAR(10)
RPAD("C1",5,'
') → '
'
In the above example, the data type of the execution result of RPAD is VARCHAR(10). Because each character is 3 bytes, the number of characters of the execution result is not the specified number of characters (5).
RPAD(CAST("C1" AS VARCHAR(15)),5,'
') → '
'
In the above example, the data type of the execution result of RPAD is VARCHAR(15). Because each character is 3 bytes, the number of characters of the execution result is the specified number of characters (5).
(4) Example
- Example:
-
Column C1 of table T1 is a column of the VARCHAR(8) type. In column C1, for each row containing a character string shorter than 8 characters, add an appropriate number of 0s to the right of each character string so that all rows in the column contain an 8-character string.
SELECT RPAD("C1",8,'0') FROM "T1"