Contents
trim()
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]stringtext[,characterstext] ) →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
- PostgreSQL 6.1
- added (commit 3c2d74d2)
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)
References
- PostgreSQL documentation: SQL String Functions and Operators