Introduction to PostgreSQL TRIM() function

TheTRIM() function allows you to remove specified prefixes or suffixes (or both) from a string.

Here’s the basic syntax of theTRIM() function:

TRIM([LEADING | TRAILING | BOTH] trim_characterFROM source_string)

In this syntax:

• source_string: Specify the string that you want to remove specified characters.
• trim_character: Specify the trim characters.
• LEADING: This option instructs the function to remove the leading occurrences of the specified trim character.
• TRAILING: This option instructs the function to remove trailing occurrences of the specified trim character.
• BOTH: This option instructs the function to remove both leading and trailing occurrences of the specified trim character.

TheTRIM() function can be very useful when you want to clean up strings.

To remove specific characters from the beginning of a string, you use theLTRIM() function. To remove specific characters from the end of a string, you can use theRTRIM() function.

PostgreSQL TRIM() function examples

Let’s explore some examples of using theTRIM() function.

1) Basic PostgreSQL TRIM() function example

The following example uses theTRIM() function to remove leading and trailing spaces from the string' PostgreSQL ':

SELECTTRIM('   PostgreSQL   ') AS trimmed_string;

Output:

trimmed_string---------------- PostgreSQL(1 row)

The output is a string without leading and trailing spaces.

2) Using the PostgreSQL TRIM() function to remove specific characters

The following example uses theTRIM() function to remove leading and trailing hash symbols (#) from the string'##PostgreSQL##':

SELECT TRIM('#' FROM '##PostgreSQL##')AS trimmed_string;

Output:

trimmed_string---------------- PostgreSQL(1 row)

3) Using the TRIM() function to remove specific characters by specifying the trim location

The following example uses the PostgreSQLTRIM() function to remove leading, trailing, and both leading and trailing zeros from the string'0000123450':

SELECT TRIM(LEADING '0' FROM '000123450')AS trimmed_string_leading,       TRIM(TRAILING '0' FROM '000123450') AS trimmed_string_trailing,       TRIM(BOTH '0' FROM '000123450') AS trimmed_string_both;

Output:

trimmed_string_leading| trimmed_string_trailing| trimmed_string_both------------------------+-------------------------+--------------------- 123450                 | 00012345                | 12345(1 row)

4) Using the TRIM() function with table data

First,create a table calledtodo andinsert some sample data:

CREATE TABLE todo(  id SERIAL PRIMARY KEY,  title VARCHAR(255)NOT NULL,  completed BOOL NOT NULL DEFAULT false);INSERT INTO todo(title)VALUES  ('   Learn PostgreSQL   '),  ('Build an App   ')RETURNING *;

Output:

id |         title          | completed----+------------------------+-----------  1 |    Learn PostgreSQL    | f  2 | Build an App           | f(2 rows)

Second, remove the leading and trailing spaces from thetitle column using theTRIM() function:

UPDATE todoSET title = TRIM(title);

Output:

UPDATE 2

Third, verify the updates:

SELECT * FROM todo;

Output:

id |      title       | completed----+------------------+-----------  1 | Learn PostgreSQL | f  2 | Build an App     | f(2 rows)

Summary

• Use the PostgreSQLTRIM() function to remove a specified leading, trailing, or both leading and trailing characters from a string.

Last updated onJanuary 27, 2024

On this page

• Introduction to PostgreSQL TRIM() function
• PostgreSQL TRIM() function examples
• Summary