RTrim Function in Postgres SQL
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
RTRIMfunction 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
WHERE. We’ll work some with
ARRAYvariable type, the
RAISE NOTICE, and string concatenation via the
CONCATfunction, as well as a short-hand version where we use
||to combine strings.
- Practice of the use of simple SQL statements, including
- 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
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.
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
-- loop through our four phrases
WHILE i_current_array_spot < i_size_of_array
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;
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.
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
In the example above, we gave the CONCAT function three parameters to combine. Using the syntax above but making the example slightly more realistic:
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.
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:
Let’s now use both the RTrim() and Concat() functions in SQL in conjunction with the SELECT, FROM, and WHERE clauses:
CONCAT(RTRIM(t_name_first), " ", RTRIM(t_name_last))
is_user_active = TRUE;
And now, using the more simple version of concatenation:
RTRIM(t_name_first) || RTRIM(t_name_last)
is_user_active = TRUE;
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
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