PostgreSQL provides various in-built functions to perform different tasks on the strings. For instance, the SUBSTRING() function extracts a substring, the POSITION() function finds the location/position of a substring within the input string, and so on. Similarly, Postgres offers another useful built-in function named TRIM() that deletes the extra spaces from the leading(starting), trailing(ending) or both sides of the string.

This blog post will demonstrate the working of the TRIM() function via practical examples. So, let’s get started!

How to Use TRIM() Function in PostgreSQL?

TRIM() is an inbuilt function in Postgres that accepts a string and deletes the leading, trailing, or both leading and trailing spaces or characters from a string. Here is the syntax that will remove all your ambiguities regarding the TRIM() function:

TRIM(flag rem_string FROM input_string);

In the above syntax, TRIM() is a built-in function that accepts three parameters:

• In place of the flag, you can specify LEADING, TRAILING, or BOTH.
• Specifying the LEADING parameter will remove the spaces/characters from the beginning, while if you specify the TRAILING parameter, then the spaces/characters will be removed from the end of the string.
• In case you specify BOTH, then the spaces/characters will be trimmed from both sides(i.e., Start and end) of the input string.
• rem_string is an optional parameter that can be skipped. The rem_string parameter removes a particular set of characters from the input string.
• input_string represents the main string.

Alternatively, you can use the below-listed shorter version of the TRIM() function in Postgres:

• The LTRIM() is a shorter function to remove the character’s sequence and spaces from the start of the string.
• The RTRIM() is a shorter function of TRIM() that removes the character’s sequence and spaces from the end of the string.
• The BTRIM() function is a shorter function that is a combination of the LTRIM() and RTRIM() functions, and hence it trims the characters and spaces from both sides, i.e., leading and trailing.

Example 1: How Does the TRIM() Function Work in Postgres?

Let’s utilize the TRIM() function to remove the leading/starting and trailing/ending spaces from the given string “ Welcome to commandprompt.com ”:

SELECT TRIM(' Welcome to commandprompt.com ');

The output clarifies that the TRIM() function successfully trimmed the leading and trailing spaces from the input string.

Example 2: How to Remove Trailing Characters From a String in Postgres?

In the following code snippet, we will remove the trailing characters '.com' from the input string using the TRIM() function:

SELECT TRIM(TRAILING '.com' FROM 'Welcome to commandprompt.com');

This way, you can trim a sequence of characters from the input string via the TRIM() function.

Example 3: How to Remove Leading Characters From a String in Postgres?

In this example, we will remove the leading "wel" from the input string using TRIM() function:

SELECT TRIM(LEADING 'Wel' FROM 'Welcome to commandprompt.com');

The output shows that the TRIM() function successfully trimmed the sequence of characters from the input string.

Example 4: How to Use TRIM() Function on Table’s Data?

Let’s create a sample table named “emp_data” and insert some data into that table:

CREATE TABLE emp_data(
emp_name TEXT,
emp_email VARCHAR
);

Now insert some data into the emp_data table via the following command:

INSERT INTO emp_data(emp_name, emp_email)
VALUES
('Tim Root', 'timroot@xyz.com'),
('Mike William', 'mikewilliam@xyz.com'),
('Joe Clarke', 'joeclarke@xyz.com');

Let’s utilize the TRIM() function on the emp_email column for trimming the trailing sequence of characters, i.e., “.com”:

SELECT emp_email, TRIM(TRAILING '.com' FROM emp_email)
FROM emp_data;

The output authenticates that the TRIM() function successfully trimmed the specified trailing characters from the emp_email column.

Conclusion

TRIM() is an inbuilt function in Postgres that accepts a string and deletes the leading, trailing, or both leading and trailing spaces or characters from a string. This blog post demonstrated various use cases of the TRIM() function via practical examples.