Postgres Char_Length Function in SQL

Introduction

In this article, we learn to use the Postgres Char_Length function when manipulating strings with SQL to determine strength length, including:

• What? What does the CHAR_LENGTH function do?
• How? How do we use this function in our PostgreSQL query execution?
• Why? When do we need this function? A real world use case is included.
• Extra We’ll also take a brief look at the related functions, LEFT, RIGHT, and UPPER.

Prerequisites

• Basic understanding of how to write SQL with Postgres (or MS SQL Server, MySQL, Oracle, etc.), using the free PostgreSQL admin tool or another relational database administration tool like DBeaver, or by via code or script languages like PHP, Python, Javascript, Java, C#, ASP.Net, VB.Net, Note.js, Ruby, etc.) that allows a connection to your database as well as a method for sending it PL/SQL commands or requests, to query or make changes to a database.
• Knowledge of the use of basic SQL (PL/SQL) statements, including SELECT and FROM.
• Understanding of what text types, strings, and concatenation mean.

What is the CHAR_LENGTH() function?

The PostgreSQL CHAR_LENGTH function returns the length of a text string. So if you seed the function with something like, “PostgreSQL rocks,” the CHAR_LENGTH() function will return “16” because that is the length of the string supplied.

How to use this function

Syntax

Let’s begin with a simple example utilizing the Char_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 in the t_field_data field is less than 10 characters.

Let’s go a bit deeper now by writing a PostgreSQL function that will capitalize the first letter of a phrase.

Analysis of the above SQL, line by line:

• CREATE: In this line of our SQL code we are naming our function as “fn_init_caps” and setting the function up to receive data into a variable name of “t_phrase”, 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”).
• DECLARE: Where we declare the one extra variable, “t_letter_first” that we use within this function.
• t_letter_first =: Here we use 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 =: Here we concatenate two strings: (1) We use 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 CHAR_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_letter_first”).
• RETURN: Sends the new, capitalized phrase back to whatever part of our SQL that called this function.

Real world use of the CHAR_LENGTH() function

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

Our goal is to return the contents of the tcategory field, but without the last word _if that word is “Best”. First we’ll make a plan.

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

Analysis:

• SELECT LEFT: Beginning with text pulled from the t_category column in our database, we know this string has ” Best” at the end because of our WHERE clause below. 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 calculate length using the CHAR_LENGTH function. Our formula takes the full 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 our data acquisition.
• WHERE RIGHT: Here we are telling PostgreSQL that we want to filter our recordset (return only certain rows) based on the rows needing to have the last 5 characters in the t_category column be equal to ” Best”.

Miscellany

• In many situations, like arrays, for example, we start counting at zero. So it is important to keep in mind that with CHAR_LENGTH(), we start counting size at the value of one, not zero.
• For compatibility’s sake, Postgres has an identical function named LENGTH.
• Did you notice and wonder about 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 the practice can increase your efficiency as a programmer.

Conclusion

In this article we explored how to use the CHAR_LENGTH() function in PostgreSQL. We also learned via a real world example. Finally, we touched on:

• Creating our own Postgres function.
• Using LEFT, RIGHT, concatenation, and UPPER.