Lpad in CockroachDB

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

Introduction

Here we will learn to use Lpad in CockroachDB to achieve various text- and integer- related tasks within Cockroach SQL, including:

  • What? What is the syntax of the LPAD function and what does the function do? How do we use the Lpad() function in our CockroachDB queries? What parameters does it use?
  • Why? When do we need this function in our queries?
  • Extras We’ll also get exposure to basic SQL commands including SELECT, FROM, and WHERE. We’ll also work some with the ARRAY type, the ARRAY_LENGTH function, the WHILE loop, RAISE NOTICE, and how to cast an integer as text.

LPAD in Cockroach SQL

We use CockroachDB’s LPAD function to “pad” a text string with whatever number of characters it takes to obtain a resulting pre-determined string length. As we will see in the second example below, sometimes we want to pad numbers with zeros in order to obtain display uniformity, especially if you like to right justify numeric and/or date values.

LPAD syntax

Syntax of LPAD function:

1
txtResult = LPAD(txtSourceValue, intResultLength, txtCharForPadding);

We can give the LPAD function the following three parameters:

  • txtSourceValue: The string we feed to the CockroachDB LPAD function.
  • intResultLength: Tells LPAD how many total characters we want the resulting string to be in length.
  • txtCharForPadding: Determines which character to use for padding. Most often, you would want this character to be ” ” (space) or “0” (zero).

We will now use the above syntax and take a step in the direction of a bit more complexity and more knowledge. The project we’ll use here is to take four words and “pad” all of them on their left so that they are all the same total 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
-- declaration of variables
DECLARE
    arrayTechnologies TEXT[4];
    arrayTechnologies := {"Cockroach", "MS SQL", "PostgreSQL", "Python"};
    intResultLength INTEGER := 10;
    txtCharForPadding TEXT := " ";
    intCurrentSpotInArray INTEGER := 0;
    intItemsInArray INTEGER := ARRAY_LENGTH(arrayTechnologies,1); -- this comes out to 4
BEGIN
-- loop through the 4 phrases
    WHILE intCurrentSpotInArray < intItemsInArray
        LOOP
            intCurrentSpotInArray := intCurrentSpotInArray + 1;
            txtResult := LPAD(arrayTechnologies[intCurrentSpotInArray], intResultLength, txtCharForPadding);
            RAISE NOTICE txtResult;
            -- note: in some other forms of SQL, we use "Print" instead of "Raise Notice".
        END LOOP;
END

Analysis of the above code

  • DECLARE: Here we (1) Set up an array to hold our four words and name this array “arrayTechnologies”. (2) We’ve determined that the longest word in our array is “Javascript”, which is 10 characters long. We want to pad all words in our array so they end up being 10 characters long. This is why we assigned “10” to “intResultLength”. (3) For “txtCharForPadding” we chose a space, so that spaces are placed at the start of each of the words in our array until that word is a total of 10 characters in length. (4) Here we are initializing “intCurrentSpotInArray” to be an integer of zero. This variable will be used to count through our WHILE loop, pick which word we are currently working on, and end the loop. (5) “intItemsInArray” is used in our loop below to tell CockroachDB when to exit the loop.
  • WHILE: Here we set up the top of a loop to cycle from 0 to 3.
  • LOOP: Just below this line, we increase “intCurrentSpotInArray” by 1, so that the actual array item being pulled is one higher than 0 to 3; becoming 1 to 4.
  • txtResult: This is the base of our program. We’re plugging all our variables into Cockroach LPAD in order to get txtResult.
  • RAISE NOTICE: This returns “txtResult” so we will see the following output.
1
2
3
4
 Cockroach
    MS SQL
PostgreSQL
    Python

Notice how each word was “padded” to a maximum of 10 characters using the LPAD function, pushing all of the text values to the right to have a uniform right margin?

Now we will explore another common – possibly even more common – usage of the LPAD function in Cockroach SQL. We have a table with numbers ranging widely in the number of decimal places and want to pad these numbers with zeros so the values have a uniform appearance.

We will start off with the following database table we’ve named “tblTechnologies”.

txtTechnologytxtCatTechintRating
JavaLanguage4
MongoNoSQL15
PostgreSQLDatabase500
CockroachDBDatabase173
PythonLanguage1432
PHPLanguage12
JavascriptLanguage300

Now let’s write a query:

1
2
3
4
5
6
SELECT
    txtTechnology
    , txtCatTech
    , LPAD(intRating::text, 6, "0") AS txtRatingAfterPadding
FROM
    tblTechnologies

Analysis of the SQL above

  • SELECT txtTechnology: For reference, we are “printing” the columns named “txtTechnology” and “txtCatTech”.
  • LPAD: We fed the LPAD function with three parameters: “intRating” is the source, “6” is the maximum number of characters we want to get to, and “0” is the character we want CockroachDB to pad with. NOTICE the “::text” after “intRating”? The LPAD function requires a TEXT type, the “intRating” column is an INTEGER, so we use “::text” to “cast” our integer as a string!
  • AS: Here we chose to name the resulting text string as a new column, “txtRatingAfterPadding”.
  • FROM tblTechnologies: Instructs the Cockroach SQL interpretor which table to use as the source for our SQL data.

Here are the results when we run the above SQL:

txtTechnologytxtCatTechtxtRatingAfterPadding
JavaLanguage000004
MongoNoSQL000015
PostgreSQLDatabase000500
CockroachDBDatabase000173
PythonLanguage001432
PHPLanguage000012
JavascriptLanguage000300

Miscellaneous Notes

  • Did you notice we named some columns and variables with a prefix of “int” or “txt”? In this lesson, we used “int” to mean integer and “txt” to mean text or string. This is known as “naming conventions”.
  • In many situations, like with arrays in Python and other languages, we begin counting at zero. So it is important to keep in mind that with LPAD we start counting size at one, not zero. Same goes with Cockroach arrays, as you saw above in our WHILE loop example.

Conclusion

In this lesson we learned how to use the LPAD function in Cockroach SQL, including showing a couple ways we might do that. We also practiced the use of SQL commands including SELECT, WHERE, and FROM. We worked with an ARRAY data type, the ARRAY_LENGTH function, the WHILE loop, RAISE NOTICE for debugging, and finally, we learned how to convert an integer into a text string by “casting”.

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.