In PostgreSQL, the trim() function can be used to trim both sides, or a specified side, of a string. We can trim blank spaces or we can specify certain characters to trim.

It removes the longest string containing only characters from the ones we specify. If we don’t specify any characters, then it removes blank spaces.

Syntax

PostgreSQL allows us to choose between the following syntax:

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

And the following (non-standard) syntax:

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

Trimming Blank Space

Here’s a quick example to demonstrate:

SELECT trim(BOTH FROM '     Dog     ');

Result:

 btrim 
-------
 Dog

I didn’t specify any characters to trim and so the string had its white space trimmed from both sides. I also specified BOTH, meaning that both sides should be trimmed.

Interestingly, PostgreSQL names the column btrim when we don’t provide a column name. PostgreSQL actually has another function called btrim() that basically does the same thing, but with a different syntax, and without allowing us to specify only one side to trim.

Trimming Other Characters

We can trim other characters from the string by including them as a second argument:

SELECT trim(BOTH 'star-end' FROM 'start-Dog-end');

Result:

 btrim 
-------
 Dog

It doesn’t require that all specified characters are in the string, just that one of them is at the start/end of the string at any point in the trim operation.

Let’s remove the r character from our list:

SELECT trim(BOTH 'sta-end' FROM 'start-Dog-end');

Result:

 btrim  
--------
 rt-Dog

In this case, the first t was removed but not the second one. That’s because the trim operation for the front of the string stopped as soon as it reached the r character. I had removed that character from the list of characters to trim, and so it couldn’t go past that character to trim the second t. As for the end of the string, that was trimmed just the same as in the previous example.

Here’s another example to reiterate this:

SELECT trim(BOTH '123' FROM '123-Dog-12343223');

Result:

   btrim   
-----------
 -Dog-1234

As mentioned, we can include the blank space in our list of characters to trim if required:

SELECT trim(BOTH ' 123' FROM ' 123 Dog 1234 3223 ');

Result:

  btrim   
----------
 Dog 1234

In fact, if we don’t do this, and there are any blank spaces on either side of the string, then nothing would get trimmed.

Here’s an example of what I mean:

SELECT trim(BOTH '123' FROM ' 123 Dog 1234 3223 ');

Result:

        btrim        
---------------------
  123 Dog 1234 3223 

The string is returned untrimmed. That’s because I removed the blank space from the list of characters to trim. Given there was white space on both sides of the string, nothing was trimmed.

Trimming One Side

The syntax allows for us to trim just one side of the string. We can use LEADING to trim the front of the string, and TRAILING to trim the back:

SELECT 
    trim(LEADING 'star-end' FROM 'start-Dog-end'),
    trim(TRAILING 'star-end' FROM 'start-Dog-end');

Result:

  ltrim  |   rtrim   
---------+-----------
 Dog-end | start-Dog

Notice that the left column heading is ltrim and the right one is rtrim. This matches the name of two other, more specific, functions – ltrim() and rtrim() – that are limited to trimming from just one side only.

Alternative Syntax

PostgreSQL allows us to use a different (non-standard) syntax, as shown in the Syntax section above. Here’s an example of using that syntax:

SELECT trim(BOTH FROM 'start-Dog-end', 'star-end' );

Result:

 btrim 
-------
 Dog

We get the same result as using the other syntax, and the result is also the same as using btrim(), which uses a similar syntax, but without the BOTH and FROM keywords.

Here’s an example of using btrim() instead:

SELECT btrim('start-Dog-end', 'star-end' );

Result:

 btrim 
-------
 Dog

All I did was remove the BOTH and FROM keywords, and change the function name to btrim().

Passing Null Arguments

If any of the arguments are null, then null is returned:

SELECT 
    trim(BOTH null FROM ' Dog') AS "1",
    trim(BOTH ' Dog' FROM null) AS "2";

Result:

  1   |  2   
------+------
 null | null