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

on PostgreSQL • July 30th, 2022 • Write for Hevo

Postgresql Trim() Functions | Cover Image | Hevo Data

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.

Table of Contents

  1. How PostgreSQL TRIM() Function Works?
  2. PostgreSQL TRIM() Function: Practical Examples
  3. Why use PostgreSQL TRIM() Function?
  4. Conclusion

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.

LeadingRemoves trim_character from the beginning of the string.
TrailingRemoves trim_character from the end of the string.
BothRemoves trim_character from the beginning and the end of the string.

Scale your data integration effortlessly with Hevo’s Fault-Tolerant No Code Data Pipeline

As the ability of businesses to collect data explodes, data teams have a crucial role in fueling data-driven decisions. Yet, they struggle to consolidate the scattered data in their warehouse to build a single source of truth. Broken pipelines, data quality issues, bugs and errors, and lack of control and visibility over the data flow make data integration a nightmare.

1000+ data teams rely on Hevo’s Data Pipeline Platform to integrate data from over 150+ sources in a matter of minutes. Billions of data events from sources as varied as SaaS apps, Databases, File Storage, and Streaming sources can be replicated in near real-time with Hevo’s fault-tolerant architecture. What’s more – Hevo puts complete control in the hands of data teams with intuitive dashboards for pipeline monitoring, auto-schema management, and custom ingestion/loading schedules. 

This, combined with transparent pricing and 24×7 support, makes us the most special data pipeline software on review sites.

Take our 14-day free trial to experience a better way to manage data pipelines.

Get started for Free with Hevo!

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)
PostgreSQL TRIM() Function | Ltrim
Ltrim

#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)
PostgreSQL TRIM() Function | Rtrim
Rtrim

#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)
PostgreSQL TRIM() Function | Btrim
Btrim

#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)
PostgreSQL TRIM() Function | Ltrim for string

#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)
PostgreSQL TRIM() Function | Btrim for string

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.

VISIT OUR WEBSITE TO EXPLORE HEVO

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.

No-code Data Pipeline for PostgreSQL