Length Function in Postgres SQL

Introduction

In this lesson, we will learn to use the Length function when manipulating strings in Postgres SQL to determine string length, including:

• What? What does the LENGTH function do for us?
• How? How do we utilize this function in our PostgreSQL queries?
• Real world use When do we use this function? A real world use case is included.
• Extras We’ll also take a quick look at some related functions, LEFT, RIGHT, UPPER, and CHAR_LENGTH. We’ll also make use of Postgres’ CREATE FUNCTION and DECLARE statements.

Prerequisites

• Basic knowledge of how to write SQL with Postgres (or MS SQL Server, MySQL, Oracle, etc.), using one of PostgreSQL’s administration tools or another RDB admin tool, or by via automation languages like Python, Javascript, C#, Java, PHP, ASP.Net, VB.Net, Note.js, Ruby, etc. that allows a connection to your database as well as a method for sending it SQL statements to make changes to or query a database.
• Understanding of the use of basic PL/SQL statements, including SELECT, FROM, and WHERE.
• Knowledge of what text types, string variables, and concatenation mean.

What is the LENGTH() function?

The Postgres LENGTH function yields the length of the text string supplied as the one parameter. So if you send “I love PostgreSQL” to the LENGTH() function, it will return “17” because that is the length of the text we supplied.

How to use this function

Syntax

We’ll start now with an example showing the Length() function in a database context in conjunction with the WHERE clause:

The example above serves to return only rows of from the tbl_data table where the length of the text content in the t_column_data column is less than 10 characters.

We’ll now venture deeper by writing a Postgres function that will capitalize the first letter of a phrase.

Analysis of the SQL above:

• CREATE: In this part of the SQL code we name the function as “fn_change_to_initital_cap” and setting the function up to receive data into a variable name of “t_phrase_result”, as well as setting that variable to be of the text type.
• RETURNS: Establishes the type of data returned (“text”) and the variable name (“t_phrase_result”).
• DECLARE: Where we set up the one variable, “t_character_initial”, that we use within this function.
• t_character_initial: We utilize the LEFT() function, along with the parameter of “1” in order to determine the first character in the seed phrase, so we can later capitalize it and add it back to the beginning of our phrase.
• t_phrase_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 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 (“t_character_initial”).
• RETURN: Returns the new, capitalized phrase back to whatever part of our SQL that called this function.

Real world use of the LENGTH() function

Let’s look at a slightly deeper example in SQL of how LENGTH works. In our mini-project, we have a table called “company_technologies_used”. We want to return a very specific part of text from the t_categories column. We’ll pause to look at a representation of the table structure and data:

Our mini-project involves returning the contents of the tcategories field, but without the last 5 characters _if those characters are ” Best”. Let’s begin with a high level plan.

• (1) Get the data as a string.
• (2) Is ” Best” at the end of that string?
• (3) Get the length of the data.
• (4) Return the left side of the data without the ” Best” part.

Analysis:

• SELECT LEFT: Starting with string pulled from the t_categories column in our database, we know this string has ” Best” at the end because of our WHERE clause. So we need to cut off those last 5 characters, which means we want the left side of our string. But every row is going to provide a different length string, so we need to find the length using the 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 Postgres which table we are using for the source of our data.
• WHERE RIGHT: Here we are directing PostgreSQL that we want to limit the record set based on those rows needing to have the last five characters in the t_categories column be equal to ” Best”.

Miscellaneous

• In some situations, like arrays, we begin counting at zero. So it’s vital to keep in mind that with LENGTH(), we start counting size at the value “1”, not “0”.
• Postgres has an identical function named CHAR_LENGTH.
• Did you wonder why we named some variables and columns with a prefix of “i” or “t“? In this tutorial, we used “i” to mean integer and “t” to mean text or string. Here is a short tutorial that explains why in detail, including how this practice will most probably level up your efficiency and efficacy as a programmer.

Conclusion

In this tutorial we practiced use of the LENGTH() function in PostgreSQL. We even learned via a real world example. Finally, we worked with:

• Creating a Postgres function using CREATE FUNCTION and DECLARE.
• Using LEFT, RIGHT, string concatenation, and UPPER.