Trim in CockroachDB

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

Introduction

In this lesson we use the Trim in CockroachDB SQL to achieve various text/string related tasks and goals, including:

  • What? What’s the syntax of the TRIM function and what does the function do? How do we use the Trim function in our Cockroach SQL commands? What parameters does it use?
  • Why? When do we need the Trim function in our SQL?
  • Extras We’ll get exposure to various other SQL functions and statements including SELECT, FROM, and WHERE. Finally, we will work with the ARRAY variable type, the ARRAY_LENGTH function, the WHILE loop, RAISE NOTICE, and see how to CAST (convert) a number into text.

Prerequisites

  • Knowledge of how to write SQL in Cockroach, using CockroachDB’s free database admin tool or another relational database administration tool like DBeaver, a powerful and free GUI for increasing ease and control. Or with programming languages like Python, PHP, C#, Java, ASP.Net, VB.Net, Node, Ruby, etc. that provide a database connection to Cockroach as well as a method for sending SQL compatible commands to request data or make changes to the Cockroach database.
  • Basic awareness of how the following functions work: REPEAT and CONCAT, which – as you will see – can be “shorthanded” using the “||” symbol.
  • Understanding of what the following terms mean: text types, strings, and integers.
  • Knowledge of the use of basic SQL statements, including SELECT, FROM, WHILE, and WHERE.
  • Optional but important: An appreciation for naming conventions like the way you will see we prefix our variables and columns with txt, int, date, array, etc.

CockroachDB TRIM syntax

Put simply, we use CockroachDB’s TRIM function to remove spaces if used without any parameters.

1
txtResult = TRIM([LEADING OR TRAILING OR BOTH] [optional: txtStringToRemove] FROM txtStringSource);

We hand the TRIM function the following parameters:

  • LEADING: Optional. Tells Cockroach to start at the begin of txtStringSource and move forward.
  • TRAILING: Optional. Tells Cockroach to start at the end of txtStringSource and move backward.
  • BOTH: Optional. Tells Cockroach to remove the txtStringToRemove character from both the start and end of txtStringSource.
  • txtStringToRemove: Optional. Supplies TRIM with an optional string to look for. If not supplied, space (” “) – which is chr(32) – is assumed.
  • txtStringSource: Mandatory parameter. The string we feed to the CockroachDB function. This is the only parameter required.

CockroachDB TRIM examples

  • trim(LEADING FROM "  Which is your favorite food? ") returns “Which is your favorite food? “
  • trim(TRAILING FROM "  Which is your favorite food?  ") returns ” Which is your favorite food?”
  • trim(BOTH FROM " Which is your favorite food? ") returns “Which is your favorite food?”
  • trim("  Which is your favorite food?  ") returns “Which is your favorite food?” Yes, same as using “BOTH FROM”, which you may now see as redundant.

Use Cockroach Trim with Array

Let us use the syntax we just learned and apply the Cockroach TRIM function to every item in an array. We’ll also make use of the WHILE LOOP to accomplish this task.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- variable declarations
DECLARE
    arraytxtTechnologies TEXT[5];
    arraytxtTechnologies := {" Python ", " Cockroach ", " MS SQL ", " Java ", " Postgres "};
    txtStringToRemove TEXT := " ";
    intCurrentSpotInArray INTEGER := 0;
    intItemsInArray INTEGER := ARRAY_LENGTH(arraytxtTechnologies,1); -- this is 5
BEGIN
-- loop through our four phrases
    WHILE intCurrentSpotInArray < intItemsInArray
        LOOP
            intCurrentSpotInArray := intCurrentSpotInArray + 1;
            txtStringSource := arraytxtTechnologies[intCurrentSpotInArray]
            txtResult := TRIM(BOTH txtStringToRemove FROM txtStringSource);
            RAISE NOTICE txtResult;
        END LOOP;
END

Analysis

  • DECLARE: In this area we (a) Initialize and fill the “arraytxtTechnologies” Cockroach array with five items. (b) For “txtStringToRemove”, we chose a ” ” character. (c) We are setting “intCurrentSpotInArray” to be an integer of zero to get our loop begun. This will be used to iterate through the WHILE LOOP, pick the word in our CockroachDB array we are currently working on, and then END the LOOP. (d) We use “intItemsInArray” in our loop below to tell Cockroach when to leave the loop.
  • WHILE: Here we set up the loop to cycle from 0 to 4. 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 “intCurrentSpotInArray” by 1 each time, so the actual array item being pulled is one higher than 0 to 4; which become 1 to 5.
  • RAISE NOTICE: Return “txtResult” to see the following output.

Output

1
2
3
4
5
Python
Cockroach
MS SQL
Java
Postgres

Note how all spaces – front and back – were removed from each of our five array items.

Now, we will study another potential usage of the TRIM function for CockroachDB. Before we dive in, we’ll have a quickie lesson on concatenation in Cockroach.

Cockroach CONCAT function

First, as usual, we will examine the syntax of the powerful and oft-used CONCAT function.

Cockroach CONCAT syntax

1
txtStringCombined = CONCAT(txtString01, txtString02, txtString03);

Note we gave the CONCAT function three parameters to combine.

Now let’s take the syntax above and making the example just a bit more real with an example.

Cockroach CONCAT example

1
txtFullName = CONCAT(txtNameFirst, " ", txtNameLast);

The above SQL script takes the contents of “txtNameFirst”, adds a space after it, and then adds the contents of “txtNameLast” to the end. So, if txtNameFirst contained “Elon” and txtNameLast contained “Musk” then s_name_full now equals “Elon Musk”.

But what if we can’t be sure about whether extra spaces exist at the starts or ends of “txtNameFirst” and “txtNameLast” for every row in the Cockroach database? It’s best practice to anticipate and plan for this eventuality. So we will modify the equation above to include the TRIM function:

1
txtFullName = CONCAT(TRIM(txtNameFirst), " ", TRIM(txtNameLast));

In the example above, both “txtNameFirst” and “txtNameLast” are trimmed of any excess spaces before we use the CONCAT function to then combine the two strings.

Note: A short-hand method for CONCAT is to use the “||” combination of symbols as you can see here:

Cockroach CONCAT shorthand

1
txtFullName = TRIM(txtNameFirst) || " " || TRIM(txtNameLast);

Now we will use both the Trim and Concat CockroachDB functions in SQL in conjunction with the SELECT, FROM, and WHERE clauses:

1
2
3
4
5
6
SELECT
    CONCAT(TRIM(txtNameFirst), " ", TRIM(txtNameLast)) AS txtFullName
FROM
    tblData
WHERE
    boolIsUserActive = TRUE;

OR if you prefer the shorthand version:

1
2
3
4
5
6
SELECT
    TRIM(txtNameFirst) || " " || TRIM(txtNameLast) AS txtFullName
FROM
    tblData
WHERE
    boolIsUserActive = TRUE;

Analysis

Both of the last two Cockroach SQL scripts will return a full name for all users in tblData that are “active”.

Conclusion

In this lesson presentation we learned how to use the TRIM function in our Cockroach SQL, including working through some detailed realistic business examples. We also investigated the use of the CONCAT function and various other CockroachDB query statements including SELECT, FROM, and WHERE. In addition, we played with the ARRAY type, the ARRAY_LENGTH function, WHILE loop branching, and the RAISE NOTICE function.

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.