PostgreSQL has a function called btrim() that we can use to trim both sides 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 from both sides.

We provide the string as the first argument, and the (optional) characters as the second.

Trimming Blank Space

Here’s a quick example to demonstrate:

SELECT btrim(' Dog ');

Result:

 btrim 
-------
 Dog

In this case, I didn’t specify any characters to trim and so the string had its white space trimmed from both sides.

The effect is easier to see when there are multiple blank spaces:

SELECT 
    '    Dog    ' AS "Untrimmed",
    btrim('     Dog     ') AS "Trimmed";

Result:

  Untrimmed  | Trimmed 
-------------+---------
     Dog     | Dog

Of course, we also have the option of explicitly specifying the blank space if we want, just as we have the option of specifying other characters to trim.

Trimming Other Characters

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

SELECT btrim('start-Dog-end', 'star-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 btrim('start-Dog-end', 'sta-end');

Result:

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

Notice that 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:

SELECT btrim('123-Dog-12343223', '123');

Result:

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

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

SELECT btrim(' 123 Dog 1234 3223 ', ' 123');

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 btrim(' 123 Dog 1234 3223 ', '123');

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.

Passing Null Arguments

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

SELECT 
    btrim(null, ' Dog') AS "1",
    btrim(' Dog', null) AS "2";

Result:

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