The BTRIM function removes the characters that are specified in a trim string from the beginning and end of a source string.

The schema is SYSIBM.

This function compares the binary representation of each character (consisting of one or more bytes) in the trim-string to the binary representation of each character (consisting of one or more bytes) at the beginning and end of the source string.
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 beginning and end of the string-expression.

source-string

An expression that specifies the string from which characters are to be removed.
This expression must return a built-in character string, graphic string, numeric value, Boolean value, or datetime value.
If the source string is:

• A numeric, Boolean, or datetime value, it is implicitly cast to VARCHAR before the function is evaluated
• A CLOB value, the length of the value is limited to the maximum size of a VARCHAR (SQLSTATE 22001)
• A DBCLOB value, the actual length of the value is limited to the maximum size of a VARGRAPHIC (SQLSTATE 22001)

trim-string

An expression that specifies the characters that are to be removed from the beginning and end of the source string.
The expression must return a built-in character string, graphic string, numeric value, or datetime value.
If the trim string is:

• Not a character string or graphic string, it is implicitly cast to VARCHAR before the function is evaluated
• A CLOB, the actual length of the value is limited to the maximum size of a VARCHAR (SQLSTATE 22001)
• A DBCLOB, the actual length of the value is limited to the maximum size of a VARGRAPHIC (SQLSTATE 22001)

The type of the source string determines the default trim string:

Type of source string	Default trim string
A graphic string in a DBCS or EUC database	double-byte blank
A graphic string in a Unicode database	UCS-2 blank
A FOR BIT DATA string	X'20'
All other cases	single-byte blank

Restrictions:

• If the source string is not defined as FOR BIT DATA, then the trim string cannot be defined as FOR BIT DATA (SQLSTATE 42815).
• If one parameter (source string or trim string) is character FOR BIT DATA, then the other parameter 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).

Result

The data type of the source string determines the data type of the result:

Data type of source string	Data type of result
VARCHAR or CHAR	VARCHAR
CLOB	CLOB
VARGRAPHIC or GRAPHIC	VARGRAPHIC
DBCLOB	DBCLOB

The length attribute of the data type of the result is the same as the length attribute of the data type of the source string.
The length of the result is the length of the source string minus the number of string units that were 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.

Example

The host variable BALANCE1 is of type CHAR(9) and has the value '000345.50'.
The following statement returns the value '345.5':

   SELECT BTRIM(:BALANCE1, '0'),
   FROM   SYSIBM.SYSDUMMY1

The host variable BALANCE2 is of type CHAR(9) and has the value ' 345.50'.
The following statement returns the value '345.50'.

   SELECT BTRIM(:BALANCE2),
   FROM   SYSIBM.SYSDUMMY1