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)Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

  result
----------
 PostgresCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

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

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;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

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

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

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

Try it

Output:

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

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
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Next, insert some rows into the hashtags table:

INSERT INTO
  hashtags (hashtag)
VALUES
  ('#smartphone '),
  (' #tablet '),
  (' #smartwatch ')
RETURNING
  hashtag;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

    hashtag
---------------
 #smartphone
  #tablet
  #smartwatchCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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

UPDATE hashtags
SET
  hashtag = TRIM(hashtag);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

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

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

Try it

Summary #

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