In PostgreSQL, we can use the character_length() function to return the number of characters in a given string.

It accepts one argument; the string for which to return the length.

The character_length() function is a synonym for the char_length() function and so both do the same thing. We can also use the length() function to get the same result.

Example

Here’s an example to demonstrate:

SELECT character_length('Dividend growth');

Result:

15

Using char_length()

As mentioned character_length(), char_length(), and length() do the same thing, so we can use either of them to get the same result:

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

Result:

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

Comparison with octet_length() and bit_length()

While character_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 
    'character_length()' AS "Function",
    character_length('Cat') AS "Cat",
    character_length('Cafe') AS "Cafe",
    character_length('Café') AS "Café",
    character_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
 character_length() |   3 |    4 |    4 |    5

Passing a Null Argument

Passing a null argument results in null:

\pset null 'null'
SELECT character_length(null);

Result:

null