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