PostgreSQL provides us with various string functions, including some that allow us to trim a given string on both sides or a given side.

Below are four functions that we can use to trim strings in PostgreSQL.

The TRIM() Function

The trim() function is the most versatile of the functions listed here. It allows us to trim both sides, or a specific side (both sides by default). We can also specify the character/s that we want to trim from the string (a space is the default).

The syntax goes like this:

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

Here are some examples:

\x
SELECT 
    trim('   Bull   ') AS "Default",
    trim(FROM '   Bull   ') AS "Default with FROM",
    trim(LEADING FROM '   Bull   ') AS "Leading space",
    trim(TRAILING FROM '   Bull   ') AS "Trailing space",
    trim(LEADING '-' FROM '---Bull---') AS "Leading -",
    trim(TRAILING '-' FROM '---Bull---') AS "Trailing -",
    trim(BOTH '-' FROM '---Bull---') AS "Both -",
    trim(BOTH '-+' FROM '-+-Bull-+-') AS "Both -+";

Result (using vertical output):

Default           | Bull
Default with FROM | Bull
Leading space     | Bull   
Trailing space    |    Bull
Leading -         | Bull---
Trailing -        | ---Bull
Both -            | Bull
Both -+           | Bull

The BTRIM() Function

Another function that trims both sides of a string is the btrim() function. It’s similar to trim() except that it doesn’t allow us to specify which side to trim – it trims both sides only. That said, it does allow us to specify which character/s to trim.

Here’s the syntax:

btrim ( string text [, characters text ] )

So the string comes first and the characters to trim second.

Examples (using vertical output):

SELECT 
    btrim('   Bull   ') AS "Default",
    btrim('---Bull---', '-') AS "-",
    btrim('-+-Bull-+-', '-+') AS "-+";

Result:

Default | Bull
-       | Bull
-+      | Bull

The RTRIM() Function

When we only need to trim the right side of the string, we have the option of using the rtrim() function.

Syntax:

rtrim ( string text [, characters text ] ) 

Examples (using vertical output):

SELECT 
    rtrim('   Bull   ') AS "Default",
    rtrim('---Bull---', '-') AS "-",
    rtrim('-+-Bull-+-', '-+') AS "-+";

Result:

Default |    Bull
-       | ---Bull
-+      | -+-Bull

The LTRIM() Function

We can use the ltrim() function to trim the left side of the string.

Syntax:

ltrim ( string text [, characters text ] )

Examples (using vertical output):

SELECT 
    ltrim('   Bull   ') AS "Default",
    ltrim('---Bull---', '-') AS "-",
    ltrim('-+-Bull-+-', '-+') AS "-+";

Result:

Default | Bull   
-       | Bull---
-+      | Bull-+-