In PostgreSQL, the LENGTH() function is used to calculate the string’s length. It returns the total number of characters. PostgreSQL facilitates us with two more length functions named OCTET_LENGTH() and BIT_LENGTH(). The OCTET_LENGTH() and BIT_LENGTH() functions return the string length in the form of bytes and bits, respectively.
This post will demonstrate the working of length functions with the help of multiple examples.
PostgreSQL: LENGTH() Function
Here is the basic syntax of the Postgres LENGTH() function:
LENGTH(str);
In the above snippet, the LENGTH() is a function, while str represents a string accepted by the LENGTH() function. The string can be text, a single character, char type, varchar, or ‘character varying’ type.
Example #1: How Does the LENGTH() Function Work in PostgreSQL?
Let’s pass “commandprompt.com” to the LENGTH() function and see what the output will be:
SELECT LENGTH('commandprompt.com');
The output clarifies that the LENGTH() function generated the desired result.
Example #2: Pass an Empty String to the LENGTH() Function
Postgres allows us to pass an empty string to the LENGTH() function:
SELECT LENGTH('');
The output shows the appropriateness of the LENGTH() function.
Example #3: Pass NULL to the LENGTH()
Let’s pass the NULL as a parameter to the LENGTH() function:
SELECT LENGTH(NULL);
The output shows that this time the LENGTH() function returns a null value.
Example #4: Pass a White Space to the LENGTH() Function
You can pass a white space as a parameter to the LENGTH() function as shown in the below snippet:
SELECT LENGTH(' ');
The LENGTH() function returns 1, which shows that the LENGTH() function considers the white space as a character.
Example #5: Pass a Currency Symbol to the LENGTH Function
Let’s pass the currency symbol '₩' to the LENGTH() function:
SELECT LENGTH('₩');
The output shows that the LENGTH() function returns an accurate number.
Example #6: How to Use LENGTH() Function on Table’s Data?
We have created a table named submit_article in our database. Let’s fetch the table details using the SELECT query:
SELECT * FROM submit_article;
The article_name column holds the string type data. Let's calculate the length of each string using the LENGTH() function:
SELECT LENGTH(article_name) FROM submit_article;
Using the LENGTH() function, we will find the length of each string in the article_name column:
This is how the LENGTH() function works on table’s data.
PostgreSQL: OCTET_LENGTH() Function
The OCTET_LENGTH() function takes a string/text as an argument and returns the total bytes present in the targeted string. The below syntax is used to get the string’s length in the form of bytes:
OCTET_LENGTH(str);
In example 5 of the previous section, we passed the currency symbol to the LENGTH() function. As a result, the LENGTH() function returns 1. Now let’s pass the same symbol to the OCTET_LENGTH() function, Consequently, you will observe a clear difference.
Example: Find the String’s Length in Bytes?
Let’s run the following query to get the string’s length in the form of Bytes:
SELECT OCTET_LENGTH('₩');
Although '₩' is a single character. However, it takes 3 bytes therefore, the OCTET_LENGTH() function returns 3(bytes) instead of 1(character).
PostgreSQL: BIT_LENGTH() Function
The BIT_LENGTH() function takes a string/text as an argument and returns the total bits present in the targeted string.
Example: How to Find the Number of Bits in PostgreSQL?
In this example, we will pass the same currency symbol to the BIT_LENGTH() function:
SELECT BIT_LENGTH('₩');
The output shows that the '₩' sign consists of 24 bits.
Conclusion
The LENGTH() function in PostgreSQL finds the length of a specific string. It receives a string as an argument/parameter and returns the total number of characters. PostgreSQL provides two more length functions named OCTET_LENGTH() and BIT_LENGTH(). The OCTET_LENGTH() and BIT_LENGTH() functions return the string length in the form of bytes and bits, respectively. This write-up explained how to find the string’s length in Postgres.
