RTRIM scalar function

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.

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

• Example 1: Use the RTRIM function when the host variable HELLO is defined as CHAR(6) and has a value of 'Hello '.

     VALUES RTRIM(:HELLO)

  The result is 'Hello'. When a trim-expression is not specified only blanks are removed. The host variable is declared as CHAR(9) and is blank-padded up to 9 bytes.
• Example 2: Use the RTRIM function to remove the characters specified in the trim-expression from the end of the string-expression.

     VALUES RTRIM('...$VAR$...', '$.')

  The result is '...$VAR'.
• Example 3: Use the RTRIM function to remove the characters specified in the trim-expression from the end of the string-expression.

     VALUES RTRIM('((-78.0) )', '-0. ()')

  The result is '((-78'. When removing characters and blanks, you must include a blank in the trim-expression.