Repeat in CockroachDB

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

Introduction

In this lesson we use Repeat in CockroachDB SQL to achieve various text- and numeric- related tasks, including the following: – What’s the syntax of the Cockroach REPEAT function and what does it do? How do we use the Repeat function in our CockroachDB SQL? What are the parameters it uses? – When do we need the CockroachDB REPEAT function in our queries? – Finally, we will get some exposure to various Cockroach SQL commands including SELECT, FROM, and WHERE. We’ll work some with the ARRAY variable type, the ARRAY_LENGTH function, the WHILE loop, RAISE NOTICE, and how to cast an integer to string or text data type.

Prerequisites

  • How to write SQL in Cockroach (and/or MS SQL Server, Postgres, etc.), using a GUI for CockroachDB or with server-side code or script languagess like PHP, Python, C#, ASP.Net, VB.Net, Note.js, etc. that allow a connection to your database as well as an efficient way to send SQL commands to query or make changes to your Cockroach database.

Cockroach REPEAT function

We use CockroachDB’s REPEAT function to duplicate a value a specific number of times. As we will see in an example below, sometimes we want to pad text or numbers with zeros in order to obtain visual or other kinds of uniformity.

Cockroach Repeat Syntax

1
txtResult = REPEAT(txtWhatToRepeat, intRepetitions);

Cockroach Repeat Parameters

We give the REPEAT function two parameters: – txtWhatToRepeat: The text string we feed to the Cockroach repeat function. – intRepetitions: Tells Cockroach REPEAT how many times we want the txtWhatToRepeat string to be repeated.

We’ll use the syntax and parameters above to move in the direction of more complexity and power. The realistic business project is to take four words and “pad” all of them on their left so that they are all the same length, appearing as if they are right justified.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- variable, etc. declarations
DECLARE
    arraytxtTechnologies TEXT[4];
    arraytxtTechnologies := {"Cockroach", "MS SQL", "Java", "Python"};
    intWordLength INTEGER := 0;
    intResultLength INTEGER := 9;
    txtWhatToRepeat TEXT := " ";
    intCurrentSpotInArray INTEGER := 0;
    intItemsInArray INTEGER := ARRAY_LENGTH(arraytxtTechnologies,1);
BEGIN
-- iterate (loop) through the four words in our array
    WHILE intCurrentSpotInArray < intItemsInArray
        LOOP
            intCurrentSpotInArray := intCurrentSpotInArray + 1;
            txtWord := arraytxtTechnologies[intCurrentSpotInArray]
            intWordLength := LENGTH(txtWord);
            txtResult := REPEAT(txtWhatToRepeat, intWordLength-intResultLength) || txtWord;
            RAISE NOTICE txtResult;
        END LOOP;
END

Analysis of the above code – DECLARE: Here we (a) Initialize and fill an array with data, holding our four words. We name the array “arraytxtTechnologies”. (b) We know the longest word in our Cockroach array is “Cockroach”, which is 9 characters long. We want to pad each of the four words in our array so they end up being 9 characters long, which is why we are using the space character (” “) for the padding. This is also why we assigned “9” to “intResultLength”. (c) As mentioned above for “txtWhatToRepeat”, we chose a space so spaces are REPEATed at the start of each of the words in our array until that word reaches a total of 9 in LENGTH. (d) We are initializing “intCurrentSpotInArray” to be an integer of zero to get our loop started. This variable will be used to count through the Cockroach WHILE loop in the routine, pick the word in our array we are currently working on, and close off the loop. (e) “intItemsInArray” is used in our loop below to tell CockroachDB when to exit the loop. – WHILE: Here we are setting up our loop to cycle from 0 to 3. You will see below how we add 1 to the value just before accessing which word in the array we want to modify. – LOOP: On this line we increase “intCurrentSpotInArray” by 1, so that the actual array item being pulled is one higher than 0 through 3; becoming 1 through 4. – RAISE NOTICE: Here we return “txtResult” so you can see the following output.

1
2
3
4
Cockroach
   MS SQL
     Java
   Python

See now how each word was “padded” to a maximum of 9 characters, pushing every word to have a uniform right margin?

NOTE: The Cockroach LPAD function is another way to accomplish the same goal; often with a bit more efficiency.

Let us explore another potential use for the REPEAT function in CockroachDB. The situation: A Cockroach data table with integers ranging in the number of decimal places and we want to pad these numbers with zeros so they have a uniform appearance.

We’ll start off with the following Cockroach table named “tblTechnologies”.

txtTechnologytxtCatTechintRating
JavaLanguage14
MongoNoSQL25
CockroachDBDatabase273
PythonLanguage2432
PHPLanguage22
JavascriptLanguage400
1
2
3
4
5
6
7
8
9
10
11
12
DECLARE
    intResultLength INTEGER := 7;
    txtWhatToRepeat TEXT := "0";
BEGIN
SELECT
    txtTechnology
    , txtCatTech
    , intRating::text AS txtWord
    , LENGTH(txtWord) AS intWordLength
    , REPEAT(txtWhatToRepeat, intWordLength-intResultLength) || txtWord AS txtRatingAfterPadding
FROM
    tblTechnologies

Analysis – SELECT txtTechnology: For reference, not need, we are outputting values from the columns named “txtTechnology” and “txtCatTech”. – intRating::text AS txtWord: Here we have recast (CAST) “intRating” integer to be text called “txtWord” so we can later use the Cockroach LENGTH and REPEAT functions on it. – LENGTH(txtWord) AS intWordLength: We need to know the current length of our word, as you will see on the next line of the script. Note: We could choose to calculate that on the fly, within the formula below, but we are doing it explicitly here to increase readability and ease of learning these concepts. – REPEAT(txtWhatToRepeat, intResultLength – intWordLength) || txtWord AS txtRatingAfterPadding: Seed the REPEAT function with two parameters: “txtWhatToRepeat”, which is a zero, and intWordLength – intResultLength, which tells Cockroach, “Take the length of our word and tell us how close to 7 characters it is. If our word is 6 characters long, for example, we know one zero needs to be added in order to get to 7. Then we use the concatenation symbol of “||” to add “txtWord” to the right side of those zeros and name the whole resulting string AS “txtRatingAfterPadding”. You could ask, “Why not recast “txtRatingAfterPadding” back to an integer?” That’s because we would lose the zeros we just added by going that route. – FROM tblTechnologies: tells the Cockroach database engine which table to use as the source for our SQL data.

The following results are returned when we execute the SQL:

txtTechnologytxtCatTechtxtRatingAfterPadding
JavaLanguage0000014
MongoNoSQL0000025
CockroachDBDatabase0000273
PythonLanguage0002432
PHPLanguage0000022
JavascriptLanguage0000400

Conclusion

In this lesson we gained knowledge of how to use the REPEAT function in our Cockroach SQL, including working through two detailed realistic business examples. We also practiced the use of various CockroachDB query commands, including SELECT, FROM, and WHERE. We worked with the ARRAY type, the ARRAY_LENGTH function, WHILE loop branching, the LENGTH function, RAISE NOTICE, and how to cast an integer into text using the “::” symbol.

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.