Postgres Length Function

In this PostgreSQL tutorial, I will show you how to use the Postgres length function to find the length of the given string.

Then I will explain how to use the Postgres length function with columns in the table. Additionally, you will understand the functions in PostgreSQL which return the length of the string in bits and bytes.

Postgres Length Function

PostgreSQL provides a function length() that you use to find the numbers of characters in the given string. If the string consists of five characters, then this function returns the 5 as the length of that string.

The syntax is given below.

LENGTH(string_value)

• LENGTH(): It is the function itself that computes the length of the provided string.
• string_value: It is the string value that you pass to the length function. It can be any type of string in PostgreSQL varchar, character varying, text, single character and etc.

Suppose you need to analyze the product reviews, which means you need to find the average reviews for the product to know whether customers give details feedback or just give short comments, in that case, you can use the LENGHT() function.

Maybe you have table ‘tweets’ and you need to find the length of the tweets to see how many users using the maximum character length, here LENGTH() function is very useful for finding the length of the tweets.

Or you have table ‘students’ and you want to find the length of the student’s name or you want to find the student’s name based on the specified length such as (returning the student name with a character less than 3 from the table).

You may use any tables where you need to perform a query on the tables based on the length. Let’s see with different examples how the PostgreSQL Length function works.

General Use of Postgres Length Function

The Postgres Length function returns the length of the single word, white spaces, a character and etc.

Find the length of the word ‘USA’.

SELECT LENGTH('USA');

The Postgres length() function returns the number of characters in the word ‘USA’ as 3 which is the length of that word.

Let’s provide a single character ‘a’ to the Postgres length() function.

SELECT LENGTH('a');

The Postgres length() function also considers the single character and returns the length of the single character as 1.

Now pass the white spaces to the Postgres length() function and see what happens.

SELECT LENGTH(' ');

In the above query, white space is passed to the Postgres Length function and it returns the length as 1. The Postgres length() function also considers the white spaces.

Also, check with the special characters. So pass the ‘@’ to the Postgres length() function and see whether it returns the length or not.

SELECT LENGTH('@');

The PostgreSQL length function also works on the special character, the output shows the length of the passed special character ‘@’ is 1. If you pass two special characters to the Postgres length() function, the length will be 2.

Take one more example, where you will see how the Postgres length function interprets the empty string.

The Postgre length() function returns the length of the empty string as 0 because, in the empty string, there are no characters or words.

Lastly, let’s check how the Postgres length function behaves when you pass the null value to it.

SELECT LENGTH(NULL);

The Postgres length() function returns the null as output for the null values.

Using Postgres Length Function on Tables

The Postgres length() function also works on the table column. Suppose you have a table ‘students’ with columns ‘id’, ‘name’, and ‘date_of_birth’ and you want to find the length of the name of the students.

Use the below query to find the length of the name of the students.

SELECT name, LENGTH(name) as length_of_name FROM students;

In the above query, the column ‘name’ is passed to the Postres Length function and it returns the length of the name for each student in the new column ‘length_of_name’ that you can see in the output.

For example, the name ‘Benjamin’ has 8 characters in its length and 5 in the name ‘David’ and so on for the other students’ names.

Also, you may want to find the name of the students whose names contain more than 3 characters.

SELECT name
FROM students
WHERE LENGTH(name) > 3;

The above query returns the name of the students whose name has greater than 3 characters as you can see in the above output.

Using Postgres Length Function on Integer

You learned how to find the length of strings from the above example but “how you will find or measure the length of a given number?”.

To find the length of a number, first, you will convert the number into a string using the type cast function in PostgreSQL called CAST(), and then you pass it to the Postgres length() function.

So use the below query to find the length of the date_of_birth column in a table ‘students’.

SELECT name, LENGTH(cast(date_of_birth as text)) as length_of_dob FROM students;

In the above query, the column ‘date_of_birth’ is cast into text using the cast(date_of_birth as text) function, then passed to the length function as LENGTH(cast(date_of_birth as text)) with the alias as length_of_dob.

So the Postgres length() function interpreted that date_of_birth as text and returned the number of characters in each date of birth of the student as length that you can see in the output.

Using Postgres Length Function for Bits and Bytes

PostgreSQL has additional functions BIT_LENGTH() and OCTET_LENGTH(), these functions find the length of the string in bits and bytes Unlike the LENGTH() function which returns the length in numbers.

To get the number of bits in your string, use the below syntax.

BIT_LENGTH(string_value)

Let’s take a simple example, find the number of bits in the string ‘Canda’.

SELECT BIT_LENGTH('Canada');

The above query returns the length of the string ‘Canda’ in bits as 48 because each character is considered as a byte and one byte is equal to 8 bits. So there is 6 character in the string ‘Canada’, then multiply 6 by 8, and you get 48.

Now, you have another function OCTET_LENGTH() that returns the length in bytes. The syntax is given below.

OCTET_LENGTH(string_value)

Let’s see with an example how to compute the length of the string in bytes. Again you have the string ‘Australia’ and you want to find the number of bytes in it.

SELECT OCTET_LENGTH('Australia');

The above query returns the number of bytes which is 9 in the string ‘Australia’, I already told you that one character in a string is equal to 1 byte, so the string ‘Australia’ contains a 9 character.

Conclusion

In this PostgreSQL tutorial, you have covered how to compute the length of the given string using the PostgreSQL LENGTH() function, then you applied the length function to the table column. Finally, you learned how to compute the length in bits and bytes of string using the PostgreSQL BIT_LENGTH() and OCTET_LENGTH() functions respectively.

You may also read:

• Postgresql Sum – How to use
• Postgresql cast int + Examples
• PostgreSQL Length + 12 Examples