{"id":19223,"date":"2021-08-07T08:32:29","date_gmt":"2021-08-06T22:32:29","guid":{"rendered":"https:\/\/database.guide\/?p=19223"},"modified":"2021-08-07T08:32:29","modified_gmt":"2021-08-06T22:32:29","slug":"length-function-in-oracle","status":"publish","type":"post","link":"https:\/\/database.guide\/length-function-in-oracle\/","title":{"rendered":"LENGTH() Function in Oracle"},"content":{"rendered":"\n<p>In Oracle, the <code>LENGTH()<\/code> function returns the length of its argument.<\/p>\n\n\n\n<p><code>LENGTH()<\/code> can also be thought of as a group of functions. There are five separate functions; <code>LENGTH()<\/code>, <code>LENGTHB()<\/code>, <code>LENGTHC()<\/code>, <code>LENGTH2()<\/code>, and <code>LENGTH4()<\/code>. Each function calculates the length in a different way.<\/p>\n\n\n\n<!--more-->\n\n\n\n<h2 class=\"wp-block-heading\">Syntax<\/h2>\n\n\n\n<p>The syntax goes like this:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>{ LENGTH\n| LENGTHB\n| LENGTHC\n| LENGTH2\n| LENGTH4\n}\n(char)<\/code><\/pre>\n\n\n\n<p>The functions calculate lengths as follows:<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table><thead><tr><th>Function<\/th><th>Calculates length using&#8230;<\/th><\/tr><\/thead><tbody><tr><td><code>LENGTH()<\/code><\/td><td>Characters as defined by the input character set, with the first character of string having position 1.<\/td><\/tr><tr><td><code>LENGTHB()<\/code><\/td><td>Bytes<\/td><\/tr><tr><td><code>LENGTHC()<\/code><\/td><td>Unicode complete characters<\/td><\/tr><tr><td><code>LENGTH2()<\/code><\/td><td>UCS2 code points<\/td><\/tr><tr><td><code>LENGTH4()<\/code><\/td><td>UCS4 code points<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p>The argument type can be as follows:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>When using the <code>LENGTH()<\/code> and <code>LENGTHB()<\/code> syntax, the argument can be any of the data types <code>CHAR<\/code>, <code>VARCHAR2<\/code>, <code>NCHAR<\/code>, <code>NVARCHAR2<\/code>, <code>CLOB<\/code>, or <code>NCLOB<\/code>. <\/li><li>When using the <code>LENGTHC()<\/code>, <code>LENGTH2()<\/code> and <code>LENGTH4()<\/code> syntax, the argument can be any of the data types <code>CHAR<\/code>, <code>VARCHAR2<\/code>, <code>NCHAR<\/code>, <code>NVARCHAR2<\/code> (but <em>not<\/em> <code>CLOB<\/code> or <code>NCLOB<\/code>).<\/li><\/ul>\n\n\n\n<h2 class=\"wp-block-heading\">Example<\/h2>\n\n\n\n<p>Here&#8217;s a basic example:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT LENGTH('Big fat cat')\nFROM DUAL;<\/code><\/pre>\n\n\n\n<p>Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">11<\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Compared with <code>LENGTHB()<\/code><\/h2>\n\n\n\n<p>This example shows how the results can differ, depending on which specific function you&#8217;re using, and the character set involved. <\/p>\n\n\n\n<p>In this case, we compare <code>LENGTH()<\/code> with <code>LENGTHB()<\/code>:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT \n    LENGTH('B\u00f6y\u00fck ya\u011fl\u0131 pi\u015fik') AS LENGTH,\n    LENGTHB('B\u00f6y\u00fck ya\u011fl\u0131 pi\u015fik') AS LENGTHB\nFROM DUAL;<\/code><\/pre>\n\n\n\n<p>Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">   LENGTH    LENGTHB \n_________ __________ \n       17         22<\/pre>\n\n\n\n<p>We can see that the two functions returned two different results. This is because some characters in this string use two bytes. <\/p>\n\n\n\n<p>The <code>LENGTH()<\/code> function returns the length in characters as defined by the input character set, whereas the <code>LENGTHB()<\/code> function returns the length in <em>bytes<\/em>.<\/p>\n\n\n\n<p>If we return to the original string, the results are the same between the two functions:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT \n    LENGTH('Big fat cat') AS <meta charset=\"utf-8\">LENGTH,\n    <meta charset=\"utf-8\">LENGTHB('Big fat cat') AS <meta charset=\"utf-8\">LENGTHB\nFROM DUAL;<\/code><\/pre>\n\n\n\n<p>Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">   LENGTH    LENGTHB \n_________ __________ \n       11         11 <\/pre>\n\n\n\n<p>This string uses just one byte per character, and so the length in bytes is the same as the number of characters.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Null Argument<\/h2>\n\n\n\n<p>If the argument is <code>null<\/code>, the result is <code>null<\/code>:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SET NULL 'null';\nSELECT \n    LENGTH(null) AS <meta charset=\"utf-8\">LENGTH,\n    <meta charset=\"utf-8\">LENGTHB(null) AS <meta charset=\"utf-8\">LENGTHB,\n    <meta charset=\"utf-8\">LENGTHC(<meta charset=\"utf-8\">null) AS <meta charset=\"utf-8\">LENGTHC,\n    <meta charset=\"utf-8\">LENGTH2(<meta charset=\"utf-8\">null) AS <meta charset=\"utf-8\">LENGTH2<meta charset=\"utf-8\">,\n    <meta charset=\"utf-8\">LENGTH4(<meta charset=\"utf-8\">null) AS <meta charset=\"utf-8\">LENGTH4\nFROM DUAL;<\/code><\/pre>\n\n\n\n<p>Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">   LENGTH    LENGTHB    LENGTHC    LENGTH2    LENGTH4 \n_________ __________ __________ __________ __________ \n     null       null       null       null       null <\/pre>\n\n\n\n<p>By default, SQLcl and SQL*Plus return a blank space whenever <code>null<\/code> occurs as a result of a SQL <code><a href=\"https:\/\/database.guide\/sql-select-for-beginners\/\" data-type=\"post\" data-id=\"11928\">SELECT<\/a><\/code> statement. <\/p>\n\n\n\n<p>However, you can use <code><a href=\"https:\/\/database.guide\/set-null-specify-a-string-to-return-whenever-a-null-value-occurs-in-sqlcl-sqlplus\/\">SET NULL<\/a><\/code> to specify a different string to be returned. Here I specified that the string <code>null<\/code> should be returned.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Incorrect Argument Count<\/h2>\n\n\n\n<p>Calling the function without passing any arguments results in an error:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT LENGTH()\nFROM DUAL;<\/code><\/pre>\n\n\n\n<p>Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">Error starting at line : 1 in command -\nSELECT LENGTH()\nFROM DUAL\nError at Command Line : 1 Column : 8\nError report -\nSQL Error: ORA-00909: invalid number of arguments\n00909. 00000 -  \"invalid number of arguments\"\n*Cause:    \n*Action:<\/pre>\n\n\n\n<p>And passing too many arguments also results in an error:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT LENGTH('Big fat cat', 'oops')\nFROM DUAL;<\/code><\/pre>\n\n\n\n<p>Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">Error starting at line : 1 in command -\nSELECT LENGTH('Big fat cat', 'oops')\nFROM DUAL\nError at Command Line : 1 Column : 8\nError report -\nSQL Error: ORA-00909: invalid number of arguments\n00909. 00000 -  \"invalid number of arguments\"\n*Cause:    \n*Action:<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>In Oracle, the LENGTH() function returns the length of its argument. LENGTH() can also be thought of as a group of functions. There are five separate functions; LENGTH(), LENGTHB(), LENGTHC(), LENGTH2(), and LENGTH4(). Each function calculates the length in a different way.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[41],"tags":[93,67,20],"class_list":["post-19223","post","type-post","status-publish","format-standard","hentry","category-oracle","tag-functions","tag-string-functions","tag-what-is"],"_links":{"self":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/19223","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=19223"}],"version-history":[{"count":3,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/19223\/revisions"}],"predecessor-version":[{"id":19227,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/19223\/revisions\/19227"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=19223"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=19223"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=19223"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}