PostgreSQL: trim Function

In this PostgreSQL post explains how to use the PostgreSQL trim function with syntax and examples.

Description

The PostgreSQL trim function removes all specified characters either from the beginning or the end of a string.

Syntax

The syntax for the trim function in PostgreSQL is:

trim( [ leading | trailing | both ] [ trim_character ] from string )

Parameters or Arguments

leading

Remove trim_character from the front of string.

trailing

Remove trim_character from the end of string.

both

Remove trim_character from the front and end of string.

trim_character

The set of characters that will be removed from string. If this parameter is omitted, the trim function will remove space characters from string.

string

The string to trim.

Note

• If you do not specify a value for the first parameter (leading, trailing, both), the trim function will default to both and remove the trim_character from both the front and end of string.
• If you do not specify a trim_character, the trim function will default the character to be removed as a space character.

Applies To

The trim function can be used in the following versions of PostgreSQL:

• PostgreSQL 9.4, PostgreSQL 9.3, PostgreSQL 9.2, PostgreSQL 9.1, PostgreSQL 9.0, PostgreSQL 8.4

Example

Let's look at some PostgreSQL trim function examples and explore how to use the trim function in PostgreSQL.

For example:

(Please note that for each of the example results below, we have included single quotes around the result to demonstrate what the trim function returns in PostgreSQL. If you ran these commands yourself, you would not see the single quotes in the result):

postgres=# SELECT trim(leading ' ' from '  AODBA.com  ');
       ltrim
----------------------

 'AODBA.com  '
(1 row)

postgres=# SELECT trim(trailing ' ' from '  AODBA.com  ');
       rtrim
----------------------

 '  AODBA.com'
(1 row)

postgres=# SELECT trim(both ' ' from '  AODBA.com  ');
      btrim
--------------------

 'AODBA.com'
(1 row)

postgres=# SELECT trim(' ' from '  AODBA.com  ');
      btrim
--------------------

 'AODBA.com'
(1 row)

postgres=# SELECT trim('   AODBA.com   ');
      btrim
--------------------

 'AODBA.com'
(1 row)

postgres=# SELECT trim(leading '0' from '000123');
 ltrim
-------

 '123'
(1 row)