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.”

Table of Contents

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

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

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

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

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

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.

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 give Hevo a try? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. You may also look at the amazing price, which will assist you while selecting the best plan for your requirements.

Share your experience understanding the PostgreSQL Trim() Function in the comments below! We would love to hear your thoughts.