trim()

A function which trims characters from the left or right side of a string

trim() is a system function which trims characters (default: spaces) from the left and/or right side of a string.

trim() was added in PostgreSQL 6.1.

Usage

trim ( [ LEADING | TRAILING | BOTH ] [ FROM ] string text [, characters text ] ) → text

trim() is an SQL standard function which uses keywords to separate arguments; PostgreSQL also provides the non-standard, less-verbose ltrim(), rtrim() and btrim() functions with equivalent functionality:

postgres=# SELECT trim(LEADING 'fo' FROM 'foobar'), trim(TRAILING 'bar' FROM 'foobar');
 ltrim | rtrim 
-------+-------
 bar   | foo
(1 row)

postgres=# SELECT ltrim('foobar', 'foo'), rtrim('foobar', 'bar');
 ltrim | rtrim 
-------+-------
 bar   | foo
(1 row)

trim() can also be invoked the characters to be deleted provided after a seperating comma, although this is non-standard syntax:

postgres=# SELECT trim(LEADING FROM 'foobar', 'fo'), trim(TRAILING FROM 'foobar', 'bar');
 ltrim | rtrim 
-------+-------
 bar   | foo
(1 row)

trim()  does not treat the characters to be trimmed as words or explicit sequences, nor does it care about multiple instances of the same character, so the following invocations are effectively identical:

postgres=# SELECT trim(LEADING 'fo' FROM 'foobar'), trim(LEADING 'ooofff' FROM 'foobar');
 ltrim | ltrim 
-------+-------
 bar   | bar

Characters are treated as case-sensitive.

Note that trim() is unusual in that it returns ltrim, rtrim or btrim as the derived column name, depending on invocation.

For a more flexible method of removing (or replacing) characters in a string, see regexp_replace().

Change history

Examples

Remove spaces from both ends of a string:

postgres=# SELECT trim(BOTH FROM '  foobar   ');
 btrim  
--------
 foobar
(1 row)

Remove all instances of !, ? and # from the left side of a string:

postgres=# SELECT trim(LEADING '!?#' FROM '##??!!foo##bar');
  ltrim   
----------
 foo##bar
(1 row)

Categories

System function

See also

ltrim(), trim(), btrim()