PostgreSQL BTRIM Function

Summary: in this tutorial, you’ll learn how to use the PostgreSQL BTRIM() function to remove specified characters from both ends of a string.

Introduction to the PostgreSQL BTRIM function #

The BTRIM() function allows you to remove specified characters from both ends of a string.

Here’s the syntax of the BTRIM() function:

BTRIM(string [, characters])Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The BTRIM() function accepts two parameters:

• string: The input string you want to trim.
• characters: The characters you want to remove from the start and end of the input string. If you omit the characters, it defaults to whitespaces, including spaces, tabs, newlines, or carriage returns.

The BTRIM() function returns a new string with the specified characters removed from both ends of the input string. It returns NULL if the input string or characters is NULL.

The BTRIM() function matches characters case-sensitively, so you need to ensure that the characters argument matches the case of the string.

Trimming whitespace #

The following example uses the BTRIM() function to trim whitespace from both ends of a string:

SELECT
  BTRIM(' pgtutorial.com ');Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

     btrim
----------------
 pgtutorial.comCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Trimming specific characters #

The following example uses the BTRIM() function to remove characters #@ from the start and end of a string:

SELECT
  BTRIM('##pgtutorial.com@@', '#@');Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

     btrim
----------------
 pgtutorial.comCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this example, the BTRIM() function removes the character @ and # from the start and end of the string.

Trimming data in the database #

First, create a new table called messages to store messages:

CREATE TABLE IF NOT EXISTS messages (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  message VARCHAR(255) NOT NULL
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Next, insert some rows into the messages table:

INSERT INTO
  messages (message)
VALUES
  (' Received goods at the San Jose warehouse.'),
  (
    ' Issued four pieces of iPhone 15 Pro at the San Francisco warehouse. '
  ),
  (' #Sync inventory in San Jose warehouse. ')
RETURNING
  message;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

                                message
-----------------------------------------------------------------------
  Received goods at the San Jose warehouse.
  Issued four pieces of iPhone 15 Pro at the San Francisco warehouse.
  #Sync inventory in San Jose warehouse.Code language: CSS (css)

Then, remove spaces and # from the start and end of the message column when querying data from the messages table:

SELECT
  BTRIM(message, ' #')
FROM
  messages;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

                                btrim
---------------------------------------------------------------------
 Received goods at the San Jose warehouse.
 Issued four pieces of iPhone 15 Pro at the San Francisco warehouse.
 Sync inventory in San Jose warehouse.

After that, change the values in the message column by removing the space and # using the BTRIM() in the SET clause of the UPDATE statement:

UPDATE messages
SET
  message = BTRIM(message, ' #');Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Finally, verify the update by querying data from the messages table:

SELECT
  message
FROM
  messages;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

                               message
---------------------------------------------------------------------
 Received goods at the San Jose warehouse.
 Issued four pieces of iPhone 15 Pro at the San Francisco warehouse.
 Sync inventory in San Jose warehouse.

Summary #

• Use the BTRIM() function to remove specified characters from the start and end of a string.
• The BTRIM() function removes whitespace from both ends of a string by default.
• The BTRIM() function matches characters case-sensitively.