BTRIM scalar function

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

FL 507

Passthrough-only expression: This function is passthrough-only and cannot run on Db2 for z/OS® without acceleration. For information about invoking this function, see Accelerating queries with passthrough-only expressions.

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 source-string is defined as FOR BIT DATA, the search compares each byte in trim-string to the byte at the beginning and end of source-string.

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, or datetime value. If the source string is:

A numeric 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
A DBCLOB value, the actual length of the value is limited to the maximum size of a VARGRAPHIC

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
A DBCLOB, the actual length of the value is limited to the maximum size of a VARGRAPHIC

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

Type of source string	Default trim string
A graphic string	double-byte 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.
If one parameter (source string or trim string) is character FOR BIT DATA, then the other parameter cannot be a graphic.

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.

Examples

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