PostgreSQL TRIM() Function: Syntax & Practical Examples | A 101 Guide

“According to Statista, the total volume of data was 64.2 zettabytes in 2020; it’s predicted to reach 181 zettabytes by 2025.”

In this day and age, the importance of good data collection and efficient data cleansing for better analysis has grown to become vital.

The reason is straightforward: A data-driven decision is as good as the data it is based on.

But at this rate, the data we are collecting, errors can be as typical as wrong spellings, duplicacy, or some extra spaces here and there. These errors can result in mislabeled categories or classes. For example, you might find “Class Name” and “Class  Name” both appear, but, in fact, both should be analyzed as the same category.

We use the TRIM() Function in PostgreSQL Database to oust such challenges. In this tutorial article, we will parse through some relevant cases where the PostgreSQL TRIM() Function is employed — from trimming multiple spaces to multiple characters. Let’s get started.

How PostgreSQL TRIM() Function Works?

With the help of PostgreSQL TRIM() Function, data practitioners ensure standardization of data quality wherever there is errored positioning of data. Furthermore, the function helps maintain an optimized view of data for accurate and timely data analysis.

By default, if we do not specify, the PostgreSQL TRIM() Function removes all spaces (‘’). Additionally, it is also possible to strike down a specific character in a string in the beginning or from the end.

PostgreSQL TRIM() Function’s syntax looks like this:

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

Now let us explain the meanings of the Arguments listed in the syntax query written above with some examples for each.

Leading	Removes trim_character from the beginning of the string.
Trailing	Removes trim_character from the end of the string.
Both	Removes trim_character from the beginning and the end of the string.

PostgreSQL TRIM() Function: Practical Examples

#Example 1 – Remove Extra Spacing from Beginning of String

In this example, we will use the “Leading” argument to remove extra spacing from the beginning of the string.

select TRIM(LEADING FROM '     Leading removes extra spaces/chrs from the beginning');

Output:

Output:

                        ltrim                         
------------------------------------------------------
 Leading removes extra spaces/chrs from the beginning
(1 row)

#Example 2 – Remove Extra Spacing from End of String

In this example, we will use the “Trailing” argument to remove extra spacing from the end of the string.

select TRIM(Trailing FROM 'Trailing removes the extra spaces at the end       ');

Output:

Output:

                    rtrim                     
----------------------------------------------
 Trailing removes the extra spaces at the end
(1 row)

#Example 3 – Remove Extra Spacing from Beginning & End of String

In this example, we will use the “Both” argument to remove extra spacing from the beginning and end.

select TRIM(Both FROM '       Both removes trailing spaces from beginning & end       ');

Output:

Output:

                       btrim                       
---------------------------------------------------
 Both removes trailing spaces from beginning & end
(1 row)

#Exampe 4 – Remove Character from String

In this example, we will remove the character “Re” from the given string.

SELECT trim(leading 'Re' from 'Remove');

Output:

Output:

 ltrim 
-------
 move
(1 row)

#Example 5 – Remove Character from Both Sides of String

In this example, we will remove characters from both sides of the string.

SELECT trim(both 'BB' from 'BBtrimBB');

Output:

Output:

 btrim 
-------
 trim
(1 row)

Why use PostgreSQL TRIM() Function?

PostgreSQL TRIM() Function is one of the most basic and uncomplicated functions. You can use this function to make alterations to any string in case you notice any unnecessary spaces or characters. In cases where you would find removing unwanted elements very tedious, and room for error exists, using PostgreSQL TRIM() Function is highly recommended. You can also use this function with arrays and stored procedures.

Consequently, the Trim() function is helpful and can be used in simple to sophisticated SQL scripts. It produces immediate results while also improving performance. This function is provided by all modern-day data warehousing and data cleaning systems. This function’s features make it highly significant, which is why it is often utilized.

Also, take a look at how you can work with PostgreSQL UNNEST() and PostgreSQL STRING_AGG Functions easily with the help of detailed steps and examples.

Conclusion

In this tutorial article, we parse our way through the PostgreSQL TRIM() Function — a function readily used to cleanse data to enable good data analysis. We also walk through some practical examples to better understand the function and why you should use it to prevail good data collection paradigm in your organization.

That said, copying data into a warehouse using ETL for Data Analysis may be time-consuming if you frequently utilize PostgreSQL — Hevo can help!

Hevo, No-Code Data Pipeline, is at your disposal to rescue you. You can save your engineering bandwidth by establishing a Data Pipeline and start to replicate your data from PostgreSQL to the desired warehouse using Hevo within minutes.

Want to take Hevo for a ride? Sign Up for a 14-day free trial and simplify your ETL process. You can also have a look at our unbeatable pricing that will help you choose the right plan for your business needs!

Frequently Asked Questions