Length Function in Postgres SQL
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
LENGTHfunction 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,
CHAR_LENGTH. We’ll also make use of Postgres’
- Understanding of the use of basic PL/SQL statements, including
- Knowledge of what text types, string variables, and concatenation mean.
What is the LENGTH() function?
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
i_length_of_string_seeded = LENGTH(t_string_seeded);
We’ll start now with an example showing the Length() function in a database context in conjunction with the WHERE clause:
LENGTH(t_column_data) < 10;
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.
CREATE OR REPLACE FUNCTION fn_change_to_initital_cap(t_phrase_result text)
RETURNS text AS t_phrase_result
t_character_initial = LEFT(t_phrase_result,1);
t_phrase_result = UPPER(t_character_initial) || RIGHT(t_phrase_result, LENGTH(t_phrase_result)-1);
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
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:
|Oracle||Database Relational Monolithic|
|Mongo||Database NoSQL Flexible|
|PostgreSQL||Database Relational Free Best|
|MySQL||Database Relational Free|
|Python||Language Newish Flexible Best|
|PHP||Language Old Pervasive|
|Java||Language Powerful Pervasive|
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.
LEFT(t_categories, LENGTH(t_categories) - LENGTH(" Best"))
RIGHT(t_categories, 5) = ' Best'
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”.
- 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
- 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.
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
RIGHT, string concatenation, and
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.Get Started