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
, andCHAR_LENGTH
. We’ll also make use of Postgres’CREATE FUNCTION
andDECLARE
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
, andWHERE
. - 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
1 | 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:
1 2 3 4 5 6 | SELECT t_column_data FROM tbl_data WHERE 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.
1 2 3 4 5 6 7 8 9 | CREATE OR REPLACE FUNCTION fn_change_to_initital_cap(t_phrase_result text) RETURNS text AS t_phrase_result DECLARE t_character_initial text; BEGIN t_character_initial = LEFT(t_phrase_result,1); t_phrase_result = UPPER(t_character_initial) || RIGHT(t_phrase_result, LENGTH(t_phrase_result)-1); RETURN t_phrase_result END; |
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 thetext
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:
t_name | t_categories |
---|---|
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 |
Javascript | Language Easy 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.
1 2 3 4 5 6 | SELECT LEFT(t_categories, LENGTH(t_categories) - LENGTH(" Best")) FROM company_technologies_used WHERE RIGHT(t_categories, 5) = ' Best' |
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
andDECLARE
. - Using
LEFT
,RIGHT
, string concatenation, andUPPER
.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started