8.3.17. TRIM()

Available in

DSQL, PSQL

Syntax

  1. TRIM ([<adjust>] str)
  3. <adjust> ::=  {[<where>] [what]} FROM
  5. <where> ::=  BOTH | LEADING | TRAILING
Table 139. TRIM Function Parameters
ParameterDescription

str

An expression of a string type

where

The position the substring is to be removed from — BOTH | LEADING | TRAILING. BOTH is the default

what

The substring that should be removed (multiple times if there are several matches) from the beginning, the end, or both sides of the input string str. By default it is space (‘ ‘)

Result type

VARCHAR or BLOB

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.

Examples

  1. select trim ('  Waste no space   ') from rdb$database
  2. -- returns 'Waste no space'
  4. select trim (leading from '  Waste no space   ') from rdb$database
  5. -- returns 'Waste no space   '
  7. select trim (leading '.' from '  Waste no space   ') from rdb$database
  8. -- returns '  Waste no space   '
  10. select trim (trailing '!' from 'Help!!!!') from rdb$database
  11. -- returns 'Help'
  13. select trim ('la' from 'lalala I love you Ella') from rdb$database
  14. -- returns ' I love you El'
  16. select trim ('la' from 'Lalala I love you Ella') from rdb$database
  17. -- 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.)

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.