String Length in CockroachDB

Introduction

In this lesson document, we will learn to get string Length in CockroachDB to determine the length of a text string using the Length function, including:

• What? What does the LENGTH function do for us?
• How? How do we use this function in our CockroachDB queries?
• Real world test When do we use this function in real database problems? A realistic test case is included.
• More We’ll also take a brief tour of some related functions, including LEFT, RIGHT, UPPER, and CHAR_LENGTH. We’ll also make use of the CockroachDB CREATE FUNCTION and DECLARE statements.

Prerequisites

We hope you bring a basic understanding of how to write SQL for Cockroach (or MS SQL Server, Postgres, or other relational databases that tend to have more similarities than differences), using one of CockroachDB’s GUI tools or another RDB admin tool, or by server-side languages like Python, C#, Java, PHP, ASP.Net, VB.Net, Note.js, etc. that allow a connection to your Cockroach database as well as a method for sending SQL statements to make changes to or get data from a database. Another skill that would help in best utilizing this lesson is an understanding of the use of basic SQL statements, including SELECT, WHERE, and FROM. Finally, familiarity with what text types, strings, and concatenation mean.

Cockroach LENGTH function

The Cockroach LENGTH function yields the length of the string supplied as the only parameter. So if you send “I love CockroachDB” to the LENGTH() function, it will return “18” because that is the length of the text we gave the function as a parameter.

Length function syntax

Length function example

We will start out with an example showing the Length() function in a Cockroach database context in conjunction with the WHERE clause:

The example above serves to return only rows of from the tblData table where the length of the text content in the txtColumnData column is less than 20 characters.

We’ll now explore in more depth by writing a Cockroach function that will capitalize the first letter of a string.

Analysis of the above query:

• CREATE: In this part of the Cockroach SQL script we name the function as “fnChangeToInitCap” and set the function up to receive data into a variable named “txtPhrase_result”, as well as setting that variable to be of the text data type.
• RETURNS: Establishes the type of data returned (“text”) and the variable name (“txtPhrase_result”).
• DECLARE: Where we set up the one variable, “txtCharInitial”, that we used in the function.
• txtCharInitial: We used the LEFT function, along with the parameter of “1” in order to determine the first character in the phrase supplied, so we can later capitalize it and add it back to the start of our phrase.
• txtPhrase_result: We combine two strings: (1) Using the UPPER() function on our first letter, concatenate with “||”, and (2) use the RIGHT side of our seed phrase, feeding the RIGHT() function the “how many characters” parameter by using Cockroach LENGTH to calculate the total number of characters in our seed string and subtracting 1, where 1 represents that very first character we are removing so we can replace it with our capitalized version (“txtCharInitial”).
• RETURN: Returns the new, capitalized phrase back to whatever part of our Cockroach SQL that called this function.

Realistic example of LENGTH

Now we will study a slightly deeper example in SQL of how LENGTH can work. In this little project, we have a table called “tblFirmTechUsed”. We want to return a very specific part of text from the txtCategory column. We’ll pause to look at a representation of the table structure and data:

Our goal is to return the contents of the txtCategory column, but without the last 5 characters if those characters are ” Best”. We will begin with a high level plan.

• (a) Get the data as a text string.
• (b) Find out if ” Best” is at the end of that text string.
• (c) Get the length of the data returned.
• (d) Return the left side of the data, cutting out the ” Best” section.

Analysis

• SELECT LEFT: Starting with string pulled from the txtCategory column in our database, we know this string has ” Best” at the end because of our WHERE clause. So we need to remove those last 5 characters, which means we want the left side of the text string. But every row is going to provide a different length string, so we need to find the length using the CockroachDB LENGTH function. Our formula takes the length of the data given us and reduces that length by the length of the word we are cutting, which comes out to 5 characters.
• FROM: This tells Cockroach which table we are using for the source of our records.
• WHERE RIGHT: Here we are directing CockroachDB that we want to limit the record set based on those rows needing to have the last five characters in the txtCategory column be equal to ” Best”.

Miscellaneous

• Cockroach has an identical function named CHAR_LENGTH.
• In some situations, like arrays, we start counting at zero. So it’s vital to keep in mind that with LENGTH(), we begin counting size at the value “1”, not “0”.

Conclusion

In this lesson document we learned some uses of the LENGTH function within the CockroachDB database system. We even learned via a realistic business example. Next, we worked with creating a Cockroach function using CREATE FUNCTION and DECLARE. Finally, we added use of LEFT, RIGHT, string concatenation, and UPPER to our repertoire of database knowledge.