TRIM()

Available in: DSQL, PSQL

Added in: 2.0

Changed in: 2.1

Description: Removes leading and/or trailing spaces (or optionally other strings) from the input string. Since Firebird 2.1 this function fully supports text BLOBs of any length and character set.

Result type: VARCHAR(n) or BLOB

Syntax:

TRIM ([<adjust>] str)

<adjust>  ::=  {[where] [what]} FROM

where     ::=  BOTH | LEADING | TRAILING       /* default is BOTH */

what      ::=  The substring to be removed (repeatedly if necessary)
               from str's head and/or tail. Default is ' ' (space).

Examples:

select trim ('  Waste no space   ') from rdb$database
  -- returns 'Waste no space'

select trim (leading from '  Waste no space   ') from rdb$database
  -- returns 'Waste no space   '

select trim (leading '.' from '  Waste no space   ') from rdb$database
  -- returns '  Waste no space   '

select trim (trailing '!' from 'Help!!!!') from rdb$database
  -- returns 'Help'

select trim ('la' from 'lalala I love you Ella') from rdb$database
  -- returns ' I love you El'

select trim ('la' from 'Lalala I love you Ella') from rdb$database
  -- returns 'Lalala I love you El'

Notes:

If str is a BLOB, the result is a BLOB. Otherwise, it is a VARCHAR(n) with n the formal length of str.
The substring to be removed, if specified, may not be bigger than 32767 bytes. However, if this substring is repeated at str's head or tail, the total number of bytes removed may be far greater. (The restriction on the size of the substring will be lifted in Firebird 3.)

Warning

When used on a BLOB, this function may need to load the entire object into memory. This may affect performance if huge BLOBs are involved.