RTrim Function in Postgres SQL

Introduction

In this article we use the RTrim function in Postgres SQL to attain various text-related results, including:

  • What? What is the syntax of the RTRIM function and what does it do? How do we use the RTrim() function in our PostgreSQL queries and what parameters does rtrim() use?
  • Why? Why and when do we need this function in SQL?
  • Extras We’ll also learn some other Postgre SQL statements including SELECT, FROM, and WHERE. We’ll work some with DECLARE, the ARRAY variable type, the ARRAY_LENGTH function, the WHILE, RAISE NOTICE, and string concatenation via the CONCAT function, as well as a short-hand version where we use || to combine strings.

Prerequisites

  • Familiarity with how to write basic queries in PostgreSQL, using PG’s PGadmin tool, DBeaver, or other database administration tool. And/or writing applications with programming languages like PHP, Python, Javascript, C#, Java, ASP.Net, VB.Net, Note.js, Ruby, etc.
  • Practice of the use of simple SQL statements, including SELECT, FROM, and WHERE.
  • Understanding of what the following terms mean: text types, string types, and integer types.
  • Optional: Tutorial on naming conventions that explains why we prefix our variables, column names, table names, etc. (all objects) as you see us doing here. For example, naming “tphrase_original” with the “t” you see at the beginning in order to delineate it as a “text” object and “tbl_” before table names in order to clearly mark those objects as tables.

What does the RTRIM function do in PostgreSQL and what’s the syntax?

We use PostgreSQL’s RTRIM function to remove spaces or the character of your choice from the right side of a string (text item).

Syntax of the RTRIM() function

t_result_string = RTRIM(t_string_source, [optional: t_chars_to_delete]);

We give the RTRIM function these parameters:

  • t_result_string: The contents of “t_string_source” but with spaces (or t_chars_to_delete) removed from the right side of “t_string_source”. This is the only required parameter.
  • t_chars_to_delete: Optional. Supplies RTRIM() with an optional string to look for. If not supplied, space (which is chr(32), by the way) is assumed.

Examples with parameters

  • Rtrim(" Which is your favorite database? ") returns ” Which is your favorite database?”
  • Rtrim(" Which is your favorite database? ", " ") returns ” Which is your favorite database?”
  • Rtrim(" Which is your favorite database?", "?") returns ” Which is your favorite database”
  • Rtrim(" Which is your favorite database?", "database?") returns ” Which is your favorite”

Using the RTrim function with an Array

Let’s use the syntax we just learned and apply the RTRIM function to every item in an array. We’ll make use of the WHILE LOOP to accomplish this task. Our goal is to remove any extra spaces from the right side of each word in our array.

-- variable declarations
DECLARE
    a_t_techs TEXT[4];
    a_t_techs := {"Python ", "Postgres ", "MS SQL ", "Javascript "};
    t_chars_to_delete TEXT := " ";
    i_current_array_spot INTEGER := 0;
    i_size_of_array INTEGER := ARRAY_LENGTH(a_t_techs,1); -- value of 4 because 4 items (words) in our array
BEGIN
-- loop through our four phrases
    WHILE i_current_array_spot < i_size_of_array
        LOOP
            i_current_array_spot := i_current_array_spot + 1;
            t_string_source := a_t_techs[i_current_array_spot]
            t_result := RTRIM(t_string_source, t_chars_to_delete);
            RAISE NOTICE t_result;
        END LOOP;
END

Analysis

  • DECLARE: In this part we (a) Initialize and fill the “a_t_techs” array with four words. (b) For “t_chars_to_delete”, we chose a space as the character we’ll later remove from each word. (c) We are setting “i_current_array_spot” to be an integer of zero to get the loop started. This will be used to count through the WHILE LOOP, pick the word in our array we are currently working on, and then END the LOOP. (d) We use “i_size_of_array” in the loop below to tell Postgres when to leave the loop.
  • WHILE: Here we set up the loop to cycle from 0 to 3. Below, we add 1 to the value just before accessing which word in the array we want to make changes to.
  • LOOP: This is where we increase “i_current_array_spot” by 1 each time, so the actual array item being pulled is one higher than 0 to 3; which become 1 to 4.
  • RAISE NOTICE: Return “t_result” to see the following output.

Output

'Postgres'
'MS SQL'
'Javascript'
'Python'

Note how all ending spaces are removed from each of our four array items. The apostrophes (single quotes) you see in the output above we added so you can see the ending of each output item, to see that the spaces were removed.

We’ll now try another potential usage of the RTRIM() function in PostgreSQL. Before we dive in, let’s learn how to use…

The CONCAT function

Syntax

t_string_combo = CONCAT(t_string_1, t_string_2, t_string_3);

In the example above, we gave the CONCAT function three parameters to combine. Using the syntax above but making the example slightly more realistic:

t_name_full = CONCAT(t_name_first, " ", t_name_last);

The above SQL script takes the contents of “t_name_first”, adds a space after it, and then adds the contents of “t_name_last” to the end. So, if t_name_first contained “Sam” and t_name_last contained “Nickleson” then s_name_full now equals “Sam Nickleson”.

What if we don’t know for sure about whether extra spaces exist at the ends of “t_name_first” and “t_name_last”? We recommend you anticipate and plan for that eventuality. So we’ll change the equation to use the RTRIM() function.

t_name_full = CONCAT(RTRIM(t_name_first), " ", RTRIM(t_name_last));

In the example above, both “t_name_first” and “t_name_last” are trimmed of excess spaces at the end of each string before we use the CONCAT function to combine the two text items.

IMPORTANT: A more simple method for CONCAT is to use the “||” combination of symbols. See:

t_name_full = RTRIM(t_name_first) || RTRIM(t_name_last);

Let’s now use both the RTrim() and Concat() functions in SQL in conjunction with the SELECT, FROM, and WHERE clauses:

SELECT
    CONCAT(RTRIM(t_name_first), " ", RTRIM(t_name_last))
FROM
    tbl_postgres_users
WHERE
    is_user_active = TRUE;

And now, using the more simple version of concatenation:

SELECT
    RTRIM(t_name_first) || RTRIM(t_name_last)
FROM
    tbl_postgres_users
WHERE
    is_user_active = TRUE;

Conclusion

In this article we learned how to use the RTRIM() function in our Postgres SQL, where we created some detailed real world examples. We also learned how to use a function vital to string manipulation, CONCAT(). During our journey, we also explored various PostgreSQL query statements, including SELECT, FROM, and WHERE. In addition, we played with the DECLARE SQL statement, the ARRAY type, the ARRAY_LENGTH function, the WHILE loop command, and the RAISE NOTICE statement.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.