PostgreSQL Trim with Examples

In this PostgreSQL tutorial, we are going to cover the PostgreSQL trim with a few examples and the following topics.

PostgreSQL trim string
PostgreSQL trim all spaces
PostgreSQL trim double quotes
PostgreSQL trim multiple characters
PostgreSQL trim trailing zeros
PostgreSQL trim after character
PostgreSQL trim vs btrim

Table of Contents

PostgreSQL trim string

In Postgresql, the TRIM() function is used to delete the longest string from a string that comprises a particular character. But By default, the TRIM() function removes only spaces (‘ ‘) if we don’t specify explicitly which character that we want to remove.

We may quickly delete the longest string comprising a character from the start, end, or both the start and end of a string using the TRIM() method. Remember that a string can be any of the following data types: char, varchar, and text.

The TRIM() function is very useful when we need to remove the unwanted characters or spaces from a string in the database. We generally use it for data cleansing.

The TRIM function has three variants LTRIM(), RTRIM() and BTRIM().

The LTRIM() will remove the space or character from the front of the string.

The RTRIM() will remove the space or character from the end of the string.

The BTRIM() will remove the space or character from both ends (front and end) of the string.

The syntax of trim is given below.

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

Run the below code to see how the trim function removes space from a string.

SELECT TRIM(' Postgresql');

Instead of the above code, we can achieve the same result using the BTRIM().

SELECT BTRIM(' Postgresql ');

The output of the above code is given below.

Run the code to know about “LEADING”.

SELECT TRIM(LEADING 'U' FROM 'United States');

In the above code, we are removing the character ‘U’ from the string United States.

Instead of running the above code, we will achieve the same result using the LTRIM().

SELECT LTRIM('United States','U');

The output of the code is given below.

Leading trim in PostgreSQL — PostgreSQL Leading trim

The output shows that leading means front or when we need to remove a character or space from the front of string then we use the LEADING option.

Again, remove the ‘s’ from the end of the string United States.

SELECT TRIM(TRAILING 's' FROM 'United States');

In the above code, we are removing the character ‘s’ from the string United States.

Instead of running the above code, we will achieve the same result using the RTRIM().

SELECT RTRIM('United States','s');

The output of the code is given above.

The output shows that trailing means end or when we need to remove a character or space from the end of the string then we use the TRAILING option.

Read PostgreSQL Rename Column

PostgreSQL trim all spaces

In Postgresql, the trim function can not remove all spaces, but we can achieve the functionality of removing all spaces using the REPLACE function.

Let’s remove all spaces from a string.

SELECT REPLACE(' Postgresql',' ','');

The output of the above code is given below.

This is an example, we saw, how to trim all spaces in PostgreSQL.

Read Postgresql Delete Row

PostgreSQL trim double quotes

In Postgresql, the trim function can also trim or remove the double quotes (“”) from a string by specifying the option character as double quotes “.

Let’s remove the double quotes around the string “Postgresql” using the below code.

SELECT TRIM(BOTH '"' FROM '"Postgresql"');

The output of the above code is given below.

This is how to trim double quotes in PostgreSQL.

Read PostgreSQL Length + 12 Examples

PostgreSQL trim multiple characters

In Postgresql, we can trim or remove multiple or more than one character from a string using the TRIM function.

Let’s run the below code to remove the multiple characters from a string ‘abcbabccchellocbaabc’

SELECT trim(both 'abc' from 'abcbabccchellocbaabc');

The output of the code is given below.

This is how to trim multiple characters in PostgreSQL.

Read Update query in PostgreSQL

PostgreSQL trim trailing zeros

In Postgresql, the trim function can remove or trim the zeros from the string by specifying the option TRAILING to trim function.

Now, we will remove the zeros from the string ‘abcd000’ using the below code.

SELECT trim(trailing '0' from 'abcd000');

The output of the above code is given below.

This is how to trim trailing zeros in PostgreSQL.

Read Postgresql date_trunc function

PostgreSQL trim after character

In Postgresql, the TRIM function can remove or trim from the front and end or both sides of the string. But cannot remove after character, for that we will use here REPLACE function.

To know about the REPLACE function visit our other tutorial Postgresql replace

Let’s remove the character ‘e’ from the string ‘What is the data’.

SELECT replace('What is the data','e','');

The output of the above code is given below.

This is an example of Postgresql trim after character.

Read PostgreSQL TO_NUMBER() function

PostgreSQL trim vs btrim

In Postgresql, the TRIM function is the same as BTRIM, both the function remove characters or spaces from both sides of the string.

Let’s remove the space from both ends of the string ( United States ) using the TRIM function.

SELECT TRIM(' United States ');

Now perform the same operation using the BTRIM function.

The output of the above code is given below.



SELECT BTRIM(' United States ');

The output of the above code is given below.

From both functions, we have achieved the same result, so there is not much difference between TRIM and BTRIM. The real difference is in the syntax of the code.

Here, we saw a few differences between trim vs btrim in PostgreSQL.