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
, andUPPER
.
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
andFROM
. - 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
1 | i_length_string = CHAR_LENGTH(t_string_supplied); |
Let’s begin with a simple example utilizing the Char_Length() function in a database context in conjunction with the WHERE clause:
1 2 3 4 5 6 | SELECT t_field_data FROM tbl_data WHERE CHAR_LENGTH(t_field_data) < 10; |
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.
1 2 3 4 5 6 7 8 9 | CREATE OR REPLACE FUNCTION fn_init_caps(t_phrase text) RETURNS text AS t_phrase DECLARE t_letter_first text; BEGIN t_letter_first = LEFT(t_phrase,1); t_phrase = UPPER(t_letter_first) || RIGHT(t_phrase, CHAR_LENGTH(t_phrase)-1); RETURN t_phrase END; |
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 thetext
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:
t_name | t_category |
---|---|
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 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”.
1 2 3 4 5 6 | SELECT LEFT(t_category,CHAR_LENGTH(t_category) - CHAR_LENGTH(" Best")) FROM company_tech WHERE RIGHT(t_category,5) = ' 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, andUPPER
.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started