{"id":18951,"date":"2021-07-26T07:04:33","date_gmt":"2021-07-25T21:04:33","guid":{"rendered":"https:\/\/database.guide\/?p=18951"},"modified":"2021-07-26T07:04:33","modified_gmt":"2021-07-25T21:04:33","slug":"lower-function-in-oracle","status":"publish","type":"post","link":"https:\/\/database.guide\/lower-function-in-oracle\/","title":{"rendered":"LOWER() Function in Oracle"},"content":{"rendered":"\n<p>In Oracle, the <code>LOWER()<\/code> function returns its argument with all letters in lowercase.<\/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>LOWER(char)<\/code><\/pre>\n\n\n\n<p>Where <em><code>char<\/code><\/em>&nbsp;can be of any of the data types <code>CHAR<\/code>, <code>VARCHAR2<\/code>, <code>NCHAR<\/code>, <code>NVARCHAR2<\/code>, <code>CLOB<\/code>, or <code>NCLOB<\/code>.&nbsp;<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Example<\/h2>\n\n\n\n<p>Here&#8217;s a simple example to demonstrate:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT LOWER('NEW ZEALAND')\nFROM DUAL;<\/code><\/pre>\n\n\n\n<p>Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">   LOWER('NEWZEALAND') \n______________________ \nnew zealand           <\/pre>\n\n\n\n<p>The same applies when the argument uses mixed case:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT LOWER('New Zealand')\nFROM DUAL;<\/code><\/pre>\n\n\n\n<p>Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">   LOWER('NEWZEALAND') \n______________________ \nnew zealand           <\/pre>\n\n\n\n<p>And if the argument is already lowercase, then the result is the same as the input:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT LOWER('new zealand')\nFROM DUAL;<\/code><\/pre>\n\n\n\n<p>Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">   LOWER('NEWZEALAND') \n______________________ \nnew zealand           <\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Null Values<\/h2>\n\n\n\n<p>Passing <code>null<\/code> returns <code>null<\/code>:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SET NULL 'null';\n\nSELECT LOWER(null)\nFROM DUAL;<\/code><\/pre>\n\n\n\n<p>Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">   LOWER(NULL) \n______________ \nnull          <\/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 <code>LOWER()<\/code> without passing any arguments returns an error:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT LOWER()\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 LOWER()\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 the wrong number of arguments results in an error:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT LOWER('New', 'Zealand')\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 LOWER('New', 'Zealand')\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 LOWER() function returns its argument with all letters in lowercase.<\/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-18951","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\/18951","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=18951"}],"version-history":[{"count":6,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/18951\/revisions"}],"predecessor-version":[{"id":19078,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/18951\/revisions\/19078"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=18951"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=18951"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=18951"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}