RTrim in CockroachDB

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

In this tutorial document we use RTrim in CockroachDB SQL to attain specific 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 CockroachDB SQL and what parameters does rtrim() use?
  • Why? Why and when do we need this function in SQL?
  • Extras We will also learn some other Postgre SQL statements including SELECT, FROM, and WHERE. We will 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 SQL in CockroachDB, using DBeaver, or other database administration tools. And/or writing database applications with coding languages like PHP, Python, C#, Java, ASP.Net, VB.Net, Node.js, etc.
  • Practice of the use of simple Cockroach SQL statements, including SELECT, FROM, and WHERE.
  • Solid understanding of what the following terms mean: text types, string types, and integer types.
  • Optional: Naming conventions like naming “txtPhraseOriginal” with the “txt” you see at the starting point in order to delineate it as a “text” object and “tbl” before table names in order to clearly mark those database objects as tables.

Cockroach RTRIM syntax

We use CockroachDB’s RTRIM function to remove spaces or an ASCII character of your choice from the right side of any text string.

Syntax of the RTRIM() function SQL
txtResult = RTRIM(txtSourceString_source, [optional: txtChars_to_delete]);

We give the RTRIM function the following parameters:

  • txtResult: The contents of “txtSourceString_source” but with spaces (or txtChars_to_delete) removed from the right side of “txtSourceString_source”. This is the only required parameter.
  • txtChars_to_delete: Optional parameter. Gives RTRIM() an optional string to look for to remove instead of the default space character which is CHR(32), if you care.

Examples with parameters

  • Rtrim(” What is the best Cockroach database? “) returns ” What is the best Cockroach database?”
  • Rtrim(” What is the best Cockroach database? “, ” “) returns ” What is the best Cockroach database?”
  • Rtrim(” What is the best Cockroach database?”, “?”) returns ” What is the best Cockroach database”
  • Rtrim(” What is the best Cockroach database?”, “database?”) returns ” What is the best Cockroach”

RTrim with Array

Let’s use the syntax we learned above and utilize the RTRIM function with every item in an array. We will make use of the CockroachDB WHILE LOOP to attain this goal. Our task is to remove any extra spaces from the right side of each word in our array of values.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- variable declarations
DECLARE
    arrTxtTechs TEXT[4];
    arrTxtTechs := {"Python ", "Cockroach ", "MS SQL ", "Java "};
    txtChars_to_delete TEXT := " ";
    intCurrent_array_spot INTEGER := 0;
    intSize_of_array INTEGER := ARRAY_LENGTH(arrTxtTechs,1); -- value of 4 because there are 4 items (words) in the array
BEGIN
-- loop through the 4 items
    WHILE intCurrent_array_spot < intSize_of_array
        LOOP
            intCurrent_array_spot := intCurrent_array_spot + 1;
            txtSourceString_source := arrTxtTechs[intCurrent_array_spot]
            txtResult := RTRIM(txtSourceString_source, txtChars_to_delete);
            RAISE NOTICE txtResult;
        END LOOP;
END

Analysis

  • DECLARE: In this section we (a) Initialize and fill the “arrTxtTechs” CockroachDB array with four words. (b) For “txtChars_to_delete”, we chose a space as the character we will later remove from each item. (c) We are starting “intCurrent_array_spot” to be an integer of zero to get the loop started. This will be used to iterate through the WHILE LOOP, pick the word in our array we are currently working on, and then eventually END the LOOP. (d) We use “intSize_of_array” in the loop below to tell Cockroach 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 focusing on which word in the array we want to make changes to.
  • LOOP: This is where we increase “intCurrent_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 “txtResult” to see the following output.

Output

1
2
3
4
'Cockroach'
'MS SQL'
'Java'
'Python'

Note how all ending spaces are removed from each of our four CockroachDB 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 will now try another possible usage of the RTRIM function in CockroachDB. Before we dive in, let’s learn how to use an important related function.

CONCAT in CockroachDB

Concat syntax

1
txtSourceString_combo = CONCAT(txtSourceString_1, txtSourceString_2, txtSourceString_3);

In the example above, we provided the CockroachDB CONCAT function with three parameters to combine. Using the syntax above but making the example slightly more “business realistic”:

1
txtName_full = CONCAT(txtName_first, " ", txtName_last);

The above SQL code takes the contents of “txtName_first”, adds a space after it, and then adds the contents of “txtName_last” to the end. So, if txtName_first contained “Spam” and txtName_last contained “Bickerson” then s_name_full now equals “Spam Bickerson”.

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

1
txtName_full = CONCAT(RTRIM(txtName_first), " ", RTRIM(txtName_last));

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

NOTICE: A more simple and easy method than CONCAT is to use the “||” symbol combination. See how that works here:

1
txtName_full = RTRIM(txtName_first) || RTRIM(txtName_last);

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

1
2
3
4
5
6
SELECT
    CONCAT(RTRIM(txtName_first), " ", RTRIM(txtName_last))
FROM
    tblCockroach_users
WHERE
    boolIs_user_active = TRUE;

And now, using the more simple version of concatenation:

1
2
3
4
5
6
SELECT
    RTRIM(txtName_first) || RTRIM(txtName_last)
FROM
    tblCockroach_users
WHERE
    boolIs_user_active = TRUE;

Conclusion

In this tutorial document your knowledge grew about how to use the RTRIM function in our Cockroach SQL, where we created some detailed “business reality” examples. We also learned how to use a function vital to string manipulation, CONCAT(). During our journey, we also investigated various CockroachDB SQL code 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.