{"id":36769,"date":"2024-06-30T11:19:39","date_gmt":"2024-06-30T01:19:39","guid":{"rendered":"https:\/\/database.guide\/?p=36769"},"modified":"2024-06-30T11:19:42","modified_gmt":"2024-06-30T01:19:42","slug":"how-postgresqls-character_length-function-works","status":"publish","type":"post","link":"https:\/\/database.guide\/how-postgresqls-character_length-function-works\/","title":{"rendered":"How PostgreSQL’s CHARACTER_LENGTH() Function Works"},"content":{"rendered":"\n

In PostgreSQL, we can use the character_length()<\/code> function to return the number of characters in a given string. <\/p>\n\n\n\n

It accepts one argument; the string for which to return the length.<\/p>\n\n\n\n

The character_length()<\/code> function is a synonym for the char_length()<\/a><\/code> function and so both do the same thing. We can also use the length()<\/a><\/code> function to get the same result.<\/p>\n\n\n\n\n\n\n\n

Example<\/h2>\n\n\n\n

Here’s an example to demonstrate:<\/p>\n\n\n\n

SELECT character_length('Dividend growth');<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
15<\/pre>\n\n\n\n
Using char_length()<\/code><\/h2>\n\n\n\n
As mentioned character_length()<\/code>, char_length()<\/code>, and length()<\/code> do the same thing, so we can use either of them to get the same result:<\/p>\n\n\n\n
SELECT \n    'char_length()' AS \"Function\",\n    char_length('Cat') AS \"Cat\",\n    char_length('Cafe') AS \"Cafe\",\n    char_length('Caf\u00e9') AS \"Caf\u00e9\",\n    char_length('\u0e04\u0e32\u0e40\u0e1f\u0e48') AS \"\u0e04\u0e32\u0e40\u0e1f\u0e48\"\nUNION\nSELECT \n    'character_length()',\n    character_length('Cat') AS \"Cat\",\n    character_length('Cafe') AS \"Cafe\",\n    character_length('Caf\u00e9') AS \"Caf\u00e9\",\n    character_length('\u0e04\u0e32\u0e40\u0e1f\u0e48') AS \"\u0e04\u0e32\u0e40\u0e1f\u0e48\"\nUNION\nSELECT \n    'length()',\n    length('Cat') AS \"Cat\",\n    length('Cafe') AS \"Cafe\",\n    length('Caf\u00e9') AS \"Caf\u00e9\",\n    length('\u0e04\u0e32\u0e40\u0e1f\u0e48') AS \"\u0e04\u0e32\u0e40\u0e1f\u0e48\";<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
      Function      | Cat | Cafe | Caf\u00e9 | \u0e04\u0e32\u0e40\u0e1f\u0e48 
--------------------+-----+------+------+------
 character_length() |   3 |    4 |    4 |    5
 length()           |   3 |    4 |    4 |    5
 char_length()      |   3 |    4 |    4 |    5<\/pre>\n\n\n\n
Comparison with octet_length()<\/code> and bit_length()<\/code><\/h2>\n\n\n\n
While character_length()<\/code> returns the number of characters in the string, the octet_length()<\/code> function returns the number of bytes in the string, and bit_length()<\/code> returns the number of bits. The results we get will depend on the function we use.<\/p>\n\n\n\n
Here they are compared:<\/p>\n\n\n\n
SELECT \n    'character_length()' AS \"Function\",\n    character_length('Cat') AS \"Cat\",\n    character_length('Cafe') AS \"Cafe\",\n    character_length('Caf\u00e9') AS \"Caf\u00e9\",\n    character_length('\u0e04\u0e32\u0e40\u0e1f\u0e48') AS \"\u0e04\u0e32\u0e40\u0e1f\u0e48\"\nUNION\nSELECT \n    'octet_length()',\n    octet_length('Cat') AS \"Cat\",\n    octet_length('Cafe') AS \"Cafe\",\n    octet_length('Caf\u00e9') AS \"Caf\u00e9\",\n    octet_length('\u0e04\u0e32\u0e40\u0e1f\u0e48') AS \"\u0e04\u0e32\u0e40\u0e1f\u0e48\"\nUNION\nSELECT \n    'bit_length()',\n    bit_length('Cat') AS \"Cat\",\n    bit_length('Cafe') AS \"Cafe\",\n    bit_length('Caf\u00e9') AS \"Caf\u00e9\",\n    bit_length('\u0e04\u0e32\u0e40\u0e1f\u0e48') AS \"\u0e04\u0e32\u0e40\u0e1f\u0e48\";<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
      Function      | Cat | Cafe | Caf\u00e9 | \u0e04\u0e32\u0e40\u0e1f\u0e48 
--------------------+-----+------+------+------
 bit_length()       |  24 |   32 |   40 |  120
 octet_length()     |   3 |    4 |    5 |   15
 character_length() |   3 |    4 |    4 |    5<\/pre>\n\n\n\n
Passing a Null Argument<\/h2>\n\n\n\n
Passing a null argument results in null<\/code>:<\/p>\n\n\n\n
\\pset null 'null'\nSELECT character_length(null);<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
null<\/pre>\n","protected":false},"excerpt":{"rendered":"
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 … Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[40],"tags":[93,67,20],"class_list":["post-36769","post","type-post","status-publish","format-standard","hentry","category-postgresql","tag-functions","tag-string-functions","tag-what-is"],"_links":{"self":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/36769","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/comments?post=36769"}],"version-history":[{"count":3,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/36769\/revisions"}],"predecessor-version":[{"id":36774,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/36769\/revisions\/36774"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=36769"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=36769"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=36769"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}