PostgreSQL offers various built-in functions using which we can find/get the length of the given string. These functions include the LENGTH(), OCTET_LENGTH() and BIT_LENGTH() functions. All these functions work in such a way that they take a string as input and return its length. However, there is a slight difference in the form of the output which we are going to discuss in detail, in this blog, with examples.
Let’s discuss all these functions to get the length of the string one by one.
How to Find String Length in PostgreSQL?
The need to determine string length arises while performing different string operations, including limiting the string length for CNIC, comparing strings of equal lengths, etc. To find the string length, go through one of the below-listed built-in functions:
Now we will see how these functions work.
LENGTH() Function
The LENGTH() function retrieves the length of any particular string. The string is provided to the LENGTH() function as an argument. The fundamental structure of the LENGTH() function looks like this:
LENGTH(specify_string_here)
The LENGTH() function will accept a particular string as an argument and give the length of the provided string or the number of characters in the string.
Important – Note that a white space is also counted as a character.
The following examples will demonstrate the working of the LENGTH() function more clearly.
Example 1: Understanding the LENGTH() Function
If we want to get the length of the string “PostgreSQL” we will write the following query:
SELECT LENGTH('PostgreSQL');
The LENGTH() function will return the length of the “PostgreSQL” string.
The length of “PostgreSQL” is 10. This means that there are 10 characters in the string.
Example 2: Using the LENGTH() Function on a String Having a White Space
We can pass a white space to the LENGTH() function in the single quotation marks as a string. This can be written as
SELECT LENGTH(' ');
By executing the above query, we will be able to infer how the LENGTH() function works on a white space, as depicted below.
The LENGTH() function considers a white space a character and returns its length 1.
Example 3: Using the LENGTH() Function on a String Having White Spaces
We will now execute the LENGTH() function with a string that contains whitespaces. Consider the following query:
SELECT LENGTH('This is PostgreSQL from itsLinuxFoss')
In this case, we can find some white spaces. White space is also considered as a character. The query retrieves the following outcome.
The LENGTH() function has returned the number of characters in the provided string including the white spaces.
Example 4: Using the LENGTH() Function on an Empty String
Let’s execute the LENGTH() function on the empty string. The query can be written as follows.
SELECT LENGTH('');
The empty string has no character present in it so the above query will definitely return 0.
Example 5: Using the LENGTH() Function on a NULL Value
Now, let’s observe how the output is impacted by passing the NULL value to the LENGTH() function.
SELECT LENGTH(NULL);
Now by executing the query, we will get the following output.
By passing a NULL entry to the LENGTH() function we get NULL as output.
Example 6: Using LENGTH() Function on Table’s Data
In this example, we will apply the LENGTH() function on the table named “messages”. The table is given below:
SELECT * FROM messages;
We will apply the LENGTH() function to the “msg_content” column of the “messages” table. For that, the query can be executed as follows.
SELECT msg_content, LENGTH(msg_content)
FROM messages;
In the above query, we are selecting two columns from the “messages” table. The first column is simply the “msg_content” column and the second column shows the length of the respective entries of the “msg_content” column. The output looks like the following.
The “length” column gives the length of the strings from the “msg_content” column. All the white spaces present in the “msg_content” column are also considered characters. So they are also counted while retrieving the length of the column entries/values.
OCTET_LENGTH() Function
The OCTET_LENGTH() function returns the total bytes present in the provided string. The basic structure of the OCTET_LENGTH() function looks this:
OCTET_LENGTH(specify_the_string)
The OCTET_LENGTH() function takes a string or text as an argument and returns the number of bytes present in it.
To better understand the workings of the OCTET_LENGTH() function we will consider the following example.
Example 1: Understanding the OCTET_LENGTH() Function
We will pass the “PostgreSQL” string to the OCTET_LENGTH() function to see what it returns.
SELECT OCTET_LENGTH('PostgreSQL');
The query retrieves the total string length in the output.
The output shows that the provided string contains 10 bytes.
Example 2: Using the OCTET_LENGTH() Function on Special Currency Character
Now we will implement the OCTET_LENGTH() function on a currency symbol “€”, the same as done in the below query.
SELECT OCTET_LENGTH('€');
On successful execution of the OCTET_LENGTH() function, you will get the following results:
We can notice one thing: if this symbol was provided to the LENGTH() function it would have resulted in integer 1, as it has a single character. Whereas in this case, the OCTET_LENGTH() function returns the results for the given symbol in terms of bytes i.e. “3”.
This is how the OCTET_LENGTH() function works in PostgreSQL.
BIT_LENGTH() Function
The BIT_LENGTH() function returns the total bits present in the provided string. The basic structure of the BIT_LENGTH() function looks like this:
BIT_LENGTH(specify_the_string)
The BIT_LENGTH() function takes a string or text as an argument and returns the number of bits present in it.
To better understand the workings of the BIT_LENGTH() function we will consider the following example.
Example 1: Understanding the BIT_LENGTH() Function
We will pass the “PostgreSQL” string to the BIT_LENGTH() function to see what it returns.
SELECT BIT_LENGTH('PostgreSQL');
The query retrieves the string’s length in bits as follows.
The output shows that the provided string has 80 bits.
Example 2: Using the BIT_LENGTH() Function on Special Currency Character
Now we will implement the BIT_LENGTH() function on a currency symbol “€”, the same as done in the below query.
SELECT BIT_LENGTH('€');
The query results in the following output.
We can notice one thing: if this symbol was provided to the LENGTH() function it would have resulted in integer 1, as it has a single character. Whereas in this case, the BIT_LENGTH() function returns the bits present in the given symbol i.e. 24.
This is how the BIT_LENGTH() function works in PostgreSQL.
Comparison: LENGTH() VS OCTET_LENGTH() VS BIT_LENGTH() FUNCTIONS
In this section, we will see the difference between working simultaneously. For that, we will consider the table named “example_table”. We have created a table and inserted the values using the following queries:
CREATE TABLE example_table(string_values TEXT);
INSERT INTO example_table(string_values)
VALUES('Postgres'),('This is PostgreSQL from itsLinuxFoss'),(' '), (''), (NULL), ('₩'), ('€')
We have inserted a string with a single word, a string with spaces, a string with white space, an empty string, a NULL value, and two currency symbols in the table, respectively. The table looks like the following.
Now we will implement the 3 functions(discussed above) simultaneously, to see how they work. For this purpose, we will write the following query:
SELECT string_values,
LENGTH(string_values),
OCTET_LENGTH(string_values),
BIT_LENGTH(string_values)
FROM example_table;
We have basically implemented the LENGTH(), OCTET_LENGTH(), and BIT_LENGTH() functions simultaneously on the “string_values” column of the “example_table” to observe the returned values of the three functions.
We can observe the difference in the returned values of these 3 functions and it can be inferred that the LENGTH() function gives the length of the string or the number of characters present in the string. The OCTET_LENGTH() function gives the number of bytes present in the provided string, and the BIT_LENGTH() function gives the number of bits present in the given string.
Conclusion
The length of the string can be found by using functions like LENGTH(), OCTET_LENGTH(), and BIT_LENGTH(). These functions take the string and return its length. However, the way of returning the string length is different for these functions. The LENGTH() function retrieves the string length or the number of characters present in the given string. The OCTET_LENGTH() function gives the length of the provided string in bytes, and the BIT_LENGTH() function gives the length of the given string in bits.
TUTORIALS ON LINUX, PROGRAMMING & TECHNOLOGY
