Categories:

String & Binary Functions (General)

RPAD

Right-pads a string with characters from another string, or right-pads a binary value with bytes from another binary value.

The argument (base) is padded to length length_expr with characters/bytes from the pad argument.

Syntax

RPAD(<base>, <length_expr> [, <pad>])

Arguments

base

This must be a VARCHAR or BINARY value.

length_expr

The length should be an expression that evaluates to an integer. It should specify:

  • The number of UTF-8 characters to return if the input is VARCHAR.

  • The number of bytes to return if the input is BINARY.

pad

This must be a VARCHAR or BINARY value. The type must match the data type of the base parameter. Characters (or bytes) from this parameter are used to pad the base.

Returns

The data type of the returned value is the same as the data type of the base input value (BINARY or VARCHAR).

Usage Notes

  • If the base argument is longer than length_expr, it is truncated to length length_expr.

  • The pad argument can be multiple characters/bytes long. The pad argument is repeated in the result until the desired length length_expr is reached, truncating any superfluous characters/bytes in the pad argument. If the pad argument is empty, no padding is inserted, but the result is still truncated to length length_expr.

  • When base is a string, the default pad string default is ‘ ‘ (a single blank space). When base is a binary value, the pad argument must be provided explicitly.

Collation Details

  • Collation applies to VARCHAR inputs. Collation does not apply if the input data type of the first parameter is BINARY.

  • No impact. Although collation is accepted syntactically, collations have no impact on processing. For example, languages with two-character and three-character letters (e.g. “dzs” in Hungarian, “ch” in Czech) still count those as two or three characters (not one character) for the length argument.

  • The collation of the result is the same as the collation of the input. This can be useful if the returned value is passed to another function as part of nested function calls.

  • Currently, Snowflake allows the base and pad arguments to have different collation specifiers. However, the individual collation specifiers cannot both be retained because the returned value will have only one collation specifier. Snowflake recommends that customers avoid using pad strings that have different collation from the base string.

Examples

SELECT RPAD('123.50', 20, '*-') FROM dual;

--------------------------+
 RPAD('123.50', 20, '*-') |
--------------------------+
 123.50*-*-*-*-*-*-*-     |
--------------------------+