{"id":15965,"date":"2021-04-06T08:49:33","date_gmt":"2021-04-05T22:49:33","guid":{"rendered":"https:\/\/database.guide\/?p=15965"},"modified":"2021-04-06T08:49:34","modified_gmt":"2021-04-05T22:49:34","slug":"mariadb-character_length-explained","status":"publish","type":"post","link":"https:\/\/database.guide\/mariadb-character_length-explained\/","title":{"rendered":"MariaDB CHARACTER_LENGTH() Explained"},"content":{"rendered":"\n

In MariaDB, CHARACTER_LENGTH()<\/code> is a built-in string function that returns the length of the given string argument, measured in characters. <\/p>\n\n\n\n

CHARACTER_LENGTH()<\/code> is actually a synonym for CHAR_LENGTH()<\/a><\/code>, so you can use either.<\/p>\n\n\n\n\n\n\n\n

Syntax<\/h2>\n\n\n\n

The syntax goes like this:<\/p>\n\n\n\n

CHARACTER_LENGTH(str)<\/code><\/pre>\n\n\n\n
Where str<\/code> is the string for which the length will be returned.<\/p>\n\n\n\n
Example<\/h2>\n\n\n\n
Here’s a basic example:<\/p>\n\n\n\n
SELECT CHARACTER_LENGTH('Cat');<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+-------------------------+\n| CHARACTER_LENGTH('Cat') |\n+-------------------------+\n|                       3 |\n+-------------------------+<\/pre>\n\n\n\n
In this case, the string was three characters long, and so we got 3<\/code>.<\/p>\n\n\n\n
Non-Strings<\/h2>\n\n\n\n
If the argument is not a string, it’s converted to a string.<\/p>\n\n\n\n
Here’s another example that uses a number:<\/p>\n\n\n\n
SELECT CHARACTER_LENGTH(1234);<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+------------------------+\n| CHARACTER_LENGTH(1234) |\n+------------------------+\n|                      4 |\n+------------------------+<\/pre>\n\n\n\n
Comparison with CHAR_LENGTH()<\/code><\/h2>\n\n\n\n
As mentioned, CHARACTER_LENGTH()<\/code> is a synonym for CHAR_LENGTH()<\/code>. Here it is in comparison with CHAR_LENGTH()<\/code>:<\/p>\n\n\n\n
SELECT \n    CHARACTER_LENGTH(1234),\n    CHAR_LENGTH(1234);<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+------------------------+-------------------+\n| CHARACTER_LENGTH(1234) | CHAR_LENGTH(1234) |\n+------------------------+-------------------+\n|                      4 |                 4 |\n+------------------------+-------------------+<\/pre>\n\n\n\n
Multi-Byte Characters<\/h2>\n\n\n\n
The CHARACTER_LENGTH()<\/code> function counts each character as a single character, regardless of how many bytes it uses. Therefore, characters that use two, three, or even four bytes will still be counted as one character.<\/p>\n\n\n\n
This is in contrast to functions such as LENGTH()<\/code>, which returns the number of bytes (unless it’s in Oracle mode, in which case it does the same as CHARACTER_LENGTH()<\/code> – returns the number of characters). It’s also in contrast to functions like BIT_LENGTH()<\/a><\/code> which returns the number of bits in a string.<\/p>\n\n\n\n
Here’s an example that compares these functions:<\/p>\n\n\n\n
SELECT \n    CHARACTER_LENGTH('\u0e2d'),\n    LENGTH('\u0e2d'),\n    BIT_LENGTH('\u0e2d');<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+-------------------------+---------------+-------------------+\n| CHARACTER_LENGTH('\u0e2d')   | LENGTH('\u0e2d')   | BIT_LENGTH('\u0e2d')   |\n+-------------------------+---------------+-------------------+\n|                       1 |             3 |                24 |\n+-------------------------+---------------+-------------------+<\/pre>\n\n\n\n
This Thai character (\u0e2d<\/code>) uses 3 bytes. CHARACTER_LENGTH()<\/code> returns 1, because it’s still just a single character. LENGTH()<\/code> and BIT_LENGTH()<\/code> on the other hand, return the number of bytes and bits respectively.<\/p>\n\n\n\n
This example was done using the default SQL_MODE<\/code>. As mentioned, if it had been in Oracle mode, LENGTH()<\/code> would have behaved like CHARACTER_LENGTH()<\/code> and returned 1<\/code>.<\/p>\n\n\n\n
Null Arguments<\/h2>\n\n\n\n
Passing null<\/code> returns null<\/code>:<\/p>\n\n\n\n
SELECT CHARACTER_LENGTH(null);<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+------------------------+\n| CHARACTER_LENGTH(null) |\n+------------------------+\n|                   NULL |\n+------------------------+<\/pre>\n\n\n\n
Missing Argument<\/h2>\n\n\n\n
Calling CHARACTER_LENGTH()<\/code> without passing an argument results in an error:<\/p>\n\n\n\n
SELECT CHARACTER_LENGTH();<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
ERROR 1582 (42000): Incorrect parameter count in the call to native function 'CHARACTER_LENGTH'<\/pre>\n","protected":false},"excerpt":{"rendered":"
In MariaDB, CHARACTER_LENGTH() is a built-in string function that returns the length of the given string argument, measured in characters. CHARACTER_LENGTH() is actually a synonym for CHAR_LENGTH(), so you can use either.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[117],"tags":[93,67,20],"class_list":["post-15965","post","type-post","status-publish","format-standard","hentry","category-mariadb","tag-functions","tag-string-functions","tag-what-is"],"_links":{"self":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/15965","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=15965"}],"version-history":[{"count":5,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/15965\/revisions"}],"predecessor-version":[{"id":15977,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/15965\/revisions\/15977"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=15965"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=15965"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=15965"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}