rtrim() function in PostgreSQL

The rtrim() function in PostgreSQL is used to remove the specified characters, special symbols and spaces on right side of the string. It is essentially a function to trim trailing characters or trailing space from a string.

Syntax of rtrim() function in PostgreSQL

• string: The input string from which you want to remove characters.
• characters: A string containing all characters to be removed from the right end of the input string.

Examples of rtrim() function in PostgreSQL

1.Basic Usage – remove specified characters on right side:

 

SELECT rtrim('Hello Worldxyx', 'xy');

This removes the characters ‘x’ and ‘y’ from right ends of the string ‘Hello Worldxyx’ and returns ‘Hello World’.

Output:

2. Trimming Whitespace on right side (trailing white space)

 
SELECT rtrim('Hello World  ');

By default, if you do not specify the characters to trim, rtrim() removes whitespace from right end (removes the trailing whitespaces) of the string and returns ‘Hello World’.

Output:

3. Trimming Custom Characters on the right side

 

SELECT rtrim('Hello World---', '-');

Explanation: This removes the character ‘-‘ from right end of the string ‘Hello World—‘ and returns ‘Hello World’.

Output:

4. Trimming Multiple Characters (special Characters) on the right side

 

SELECT rtrim('Hello World*!$!', '!*$');

Explanation: This removes the multiple special characters like  ‘$’, ‘!’, ’*’  from right end of the string and returns ‘Hello World’.

Output:

Apply rtrim() function over a column in PostgreSQL

To use the rtrim() function on a column in PostgreSQL, you can apply it directly in a SELECT statement.

Table: “users” – Lets take “users” table as example.

Selecting with rtrim()

You can use rtrim() to trim unwanted characters on the right side of the specific column when selecting data from the table:

 

SELECT *, rtrim(username, ' *#$&^') AS trimmed_username FROM users;

rtrim(username, ‘ *#$&^‘): This removes trailing spaces, asterisks (*), dollar ($), hash (#) etc  characters from the username column and stores the trimmed username in the separate column as shown.

Output:

Using rtrim() on a column allows you to clean up data by removing unwanted trailing characters. This can be done dynamically in SELECT queries or permanently with UPDATE statements