PostgreSQL TRIM Function

Summary: in this tutorial, you’ll learn how to use the PostgreSQL TRIM function to remove leading and/or trailing characters from a string.

Introduction to the PostgreSQL TRIM function #

The TRIM() function allows you to remove unwanted characters from a string’s start, end, or both ends.

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

TRIM ([LEADING | TRAILING | BOTH] [characters] FROM string)Copy

In this syntax:

• LEADING removes characters from the start of the string.
• TRAILING removes characters from the end of the string.
• BOTH removes characters from both ends of the string. If you don’t specify LEADING, TRAILING, or BOTH, the TRIM() function will use the BOTH option by default.
• characters: is a set of characters to remove. It defaults to spaces if you don’t specify any specific characters.

The TRIM() function returns a new string with the characters removed from the input string. It doesn’t change the input string.

The TRIM() function will return NULL if characters or strings are NULL.

Basic PostgreSQL TRIM function examples #

The following statement uses the TRIM() function to remove the character "#" from the start of the string "#postgres":

SELECT
  TRIM(
    LEADING '#'
    FROM
      '#Postgres'
  ) result;Copy

Try it

Output:

  result
----------
 PostgresCopy

The following query uses the TRIM() function to remove the character "." from the end of the string "pgtutorial.com.":

SELECT
  TRIM(
    TRAILING '.'
    FROM
      'pgtutorial.com.'
  ) result;Copy

Try it

Output:

     result
----------------
 pgtutorial.comCopy

The following example uses the TRIM() function to remove spaces from both ends of the string " pgtutorial.com ":

SELECT
  TRIM(
    BOTH ' '
    FROM
      ' pgtutorial.com '
  ) result;Copy

Try it

Output:

     result
----------------
 pgtutorial.comCopy

It’s equivalent to the following query that uses the default values:

SELECT
  TRIM(' pgtutorial.com ') result;Copy

Try it

Output:

     result
----------------
 pgtutorial.comCopy

Using the PostgreSQL TRIM() function with table data #

First, create a new table called hashtags to store hash tags:

CREATE TABLE IF NOT EXISTS hashtags (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  hashtag VARCHAR
);Copy

Try it

Next, insert some rows into the hashtags table:

INSERT INTO
  hashtags (hashtag)
VALUES
  ('#smartphone '),
  (' #tablet '),
  (' #smartwatch ')
RETURNING
  hashtag;Copy

Try it

Then, remove spaces from both ends of hashtags when retrieving data from the hashtags table:

SELECT
  TRIM(hashtag)
FROM
  hashtags
ORDER BY
  hashtag;Copy

Try it

Output:

    hashtag
---------------
 #smartphone
  #tablet
  #smartwatchCopy

After that, update the hashtags by removing spaces from both ends:

UPDATE hashtags
SET
  hashtag = TRIM(hashtag);Copy

Try it

Finally, verify the update by retrieving data from the hashtags table:

SELECT
  hashtag
FROM
  hashtags;Copy

Try it

Summary #

• Use the TRIM() function to remove leading and/or trailing characters from a string.

Was this helpful?

YesNo