DB2 Version 10.1 for Linux, UNIX, and Windows

RTRIM scalar function

The RTRIM function removes any of the specified characters from the end of a string.

The RTRIM function removes any of the characters contained in a trim-expression from the end of a string-expression. The character search compares the binary representation of each character (consisting of one or more bytes) in the trim-expression to the binary representation of each character (consisting of one or more bytes) at the end of the string-expression. The database collation does not affect the search. If the string-expression is defined as FOR BIT DATA, the search compares each byte in the trim-expression to the byte at the end of the string-expression.

Read syntax diagramSkip visual syntax diagram
>>-RTRIM--(--string-expression--+--------------------+--)------><
                                '-,--trim-expression-'      

The schema is SYSIBM. (The SYSFUN version of this function that uses a single parameter continues to be available with support for CLOB arguments.)

string-expression
An expression that specifies the source string.
  • If only one argument is specified, the expression must return a value that is a built-in CHAR, VARCHAR, GRAPHIC, VARGRAPHIC, numeric, or datetime data type. If the data type of the string-expression value is not a CHAR, VARCHAR, GRAPHIC, or VARGRAPHIC data type, the value is implicitly cast to VARCHAR before evaluating the function.
  • If both arguments are specified, the expression must return a value that is a built-in character string, graphic string, numeric, or datetime data type. If the data type of the string-expression value is not a character string or graphic string data type, the value is implicitly cast to VARCHAR before evaluating the function. The actual length of a CLOB value is limited to the maximum size of a VARCHAR data type (SQLSTATE 22001). The actual length of a DBCLOB value is limited to the maximum size of a VARGRAPHIC data type (SQLSTATE 22001).
trim-expression
An expression that specifies the characters that are being removed from the end of a string-expression. The expression must be a value that is a built-in character string, graphic string, numeric, or datetime data type.
  • If the data type of the trim-expression is not a character string or graphic string, then the value is implicitly cast to VARCHAR before evaluating the function.
  • If the data type of the trim-expression is a CLOB, then the actual length of the value is limited to the maximum size of a VARCHAR (SQLSTATE 22001).
  • If the data type of the trim-expression is a DBCLOB, then the actual length of the value is limited to the maximum size of a VARGRAPHIC (SQLSTATE 22001).
  • If the string-expression is not defined as FOR BIT DATA, then the trim-expression cannot be defined as FOR BIT DATA (SQLSTATE 42815).
When a trim-expression is not specified, the data type of the string-expression determines the default value used:
  • A double byte blank if the string-expression is a graphic string in a DBCS or EUC database
  • A UCS-2 blank if the string-expression is a graphic string in a Unicode database
  • A value of X'20' if the string-expression is a FOR BIT DATA string
  • A single-byte blank for all other cases.

If one function argument is character FOR BIT DATA, then the other argument cannot be a graphic (SQLSTATE 42846). A combination of character string and graphic string arguments can be used only in a Unicode database (SQLSTATE 42815).

The data type of the result depends on the data type of the string-expression.
  • VARCHAR if the data type is VARCHAR or CHAR
  • CLOB if the data type is CLOB
  • VARGRAPHIC if the data type is VARGRAPHIC or GRAPHIC
  • DBCLOB if the data type is DBCLOB

The length attribute of the result data type is the same as the length attribute of the string-expression data type.

The actual length of the result for character strings is the length of string-expression minus the number of bytes removed. The actual length of the result for graphic strings is the length (in number of double byte characters) of string-expression minus the number of double byte characters removed. If all of the characters are removed, the result is an empty string with a length of zero.

If any argument can be null, the result can be null; if any argument is null, the result is the null value.

Examples