PostgreSQL provides us with a handful of string functions that return the length of a given string.

But the result between these functions can be different, depending on which function we use. That’s because the “length” can be different, depending on what we’re measuring. Are we talking about the number of characters in the string? Or the number of bytes in the string? Or perhaps we want to know the number of bits in the string.

The function we use will depend on which of the above we’re trying to measure. Below are five functions that cater for each of the above questions.

The LENGTH() Function

The length() function returns the number of characters in a given string:

SELECT length('Cashflow');

Result:

8

We pass a text value and it returns an integer.

The CHAR_LENGTH() Function

The char_length() function does the same thing; it returns the number of characters in a given string:

SELECT char_length('Cashflow positive');

Result:

17

As with the length() function, we pass a text value and it returns an integer.

The CHARACTER_LENGTH() Function

PostgreSQL has yet another function that returns the number of characters in a string; the character_length() function:

SELECT character_length('Earnings potential');

Result:

18

As with the other two functions, we pass a text value and it returns an integer.

The BIT_LENGTH() Function

The bit_length() function is a bit different to the previous functions in that it returns the number of bits in the string (as opposed to the number of characters).

We pass a text value and it returns an integer:

SELECT bit_length('Earnings potential');

Result:

144

We can see that the result here is different to the result we got when we used the character_length() function in the previous example. That’s because it the number of bits is greater than the number of characters. In this case, each character has eight bits and so the string has eight times as many bits as it has characters.

The number of bits per character isn’t necessarily always eight. This will depend on the characters being used in the string. For example:

SELECT 
    bit_length('Cafe') AS "Cafe",
    bit_length('Café') AS "Café",
    bit_length('คาเฟ่') AS "คาเฟ่";

Result:

 Cafe | Café | คาเฟ่ 
------+------+------
   32 |   40 |  120

We can see that there’s quite a large difference in the number of bits between these three words, even though they all appear to consist of four characters.

The OCTET_LENGTH() Function

The octet_length() function is similar to bit_length() except that it returns the number of bytes (instead of bits) in the string.

We pass a text value and it returns an integer:

SELECT octet_length('Earnings potential');

Result:

18

In this example the number of bytes is the same as the number of characters.

Here’s another example:

SELECT 
    octet_length('Cafe') AS "Cafe",
    octet_length('Café') AS "Café",
    octet_length('คาเฟ่') AS "คาเฟ่";

Result:

 Cafe | Café | คาเฟ่ 
------+------+------
    4 |    5 |   15

This is the same example that we used with bit_length(), except that we used octet_length() instead.

A Comparison of the Functions

Here’s a quick comparison to show how the results can be different depending on whether we’re measuring the number of characters, the bit length, or the octet length:

SELECT 
    'char_length()' AS "Function",
    char_length('Cat') AS "Cat",
    char_length('Cafe') AS "Cafe",
    char_length('Café') AS "Café",
    char_length('คาเฟ่') AS "คาเฟ่"
UNION
SELECT 
    'octet_length()',
    octet_length('Cat') AS "Cat",
    octet_length('Cafe') AS "Cafe",
    octet_length('Café') AS "Café",
    octet_length('คาเฟ่') AS "คาเฟ่"
UNION
SELECT 
    'bit_length()',
    bit_length('Cat') AS "Cat",
    bit_length('Cafe') AS "Cafe",
    bit_length('Café') AS "Café",
    bit_length('คาเฟ่') AS "คาเฟ่";

Result:

    Function    | Cat | Cafe | Café | คาเฟ่ 
----------------+-----+------+------+------
 bit_length()   |  24 |   32 |   40 |  120
 octet_length() |   3 |    4 |    5 |   15
 char_length()  |   3 |    4 |    4 |    5

So we can get quite a different result, depending on what “length” we’re measuring.