In PostgreSQL, the char_length() function returns the number of characters in a string. We pass the string as an argument and the function returns the number of characters in that string.

We can also use character_length() and length() to get the same result.

Example

Here’s an example to demonstrate:

SELECT char_length('The Illusion of Power');

Result:

21

Here’s another example with various string lengths:

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

Result:

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

Using character_length()

As alluded to, character_length() is a synonym for char_length(). Therefore, we can use the two interchangeably:

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 
    'character_length()',
    character_length('Cat') AS "Cat",
    character_length('Cafe') AS "Cafe",
    character_length('Café') AS "Café",
    character_length('คาเฟ่') AS "คาเฟ่";

Result:

      Function      | Cat | Cafe | Café | คาเฟ่ 
--------------------+-----+------+------+------
 char_length()      |   3 |    4 |    4 |    5
 character_length() |   3 |    4 |    4 |    5

And there’s also a length() function that does the same thing.

char_length() vs octet_length() and bit_length()

While char_length() returns the number of characters in the string, the octet_length() function returns the number of bytes in the string, and bit_length() returns the number of bits. The results we get will depend on the function we use.

Here they are compared:

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

Passing a Null Argument

Passing a null argument results in null:

SELECT char_length(null);

Result:

 char_length 
-------------
        null