{"id":30,"date":"2020-01-08T12:00:46","date_gmt":"2020-01-08T12:00:46","guid":{"rendered":"https:\/\/molecularsciences.org\/content\/?p=30"},"modified":"2024-02-09T13:39:31","modified_gmt":"2024-02-09T18:39:31","slug":"most-commonly-used-code-in-oracle","status":"publish","type":"post","link":"https:\/\/molecularsciences.org\/content\/most-commonly-used-code-in-oracle\/","title":{"rendered":"Commonly used code in Oracle"},"content":{"rendered":"\n<h2 class=\"wp-block-heading\">Changing case<\/h2>\n\n\n\n<p>To change the first letter of the word to cap, use the inicap function. Use upper to covert to uppercase and lower to convert to lower case. Examples:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>initcap('change case')  -- Change Case\nlower('Change Case')    -- change case\nupper('change case')    -- CHANGE CASE<\/code><\/pre>\n\n\n\n<p>These functions work on Oracle 8i and above.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Concatenation<\/h2>\n\n\n\n<p>Peculiarly, Oracle uses the double pipe ( || ) as a concatenation operator. Most programming languages use || for the or operator. Example:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>insert into mytable (id, name) values (12, 'Mr. ' || lastname);\n<\/code><\/pre>\n\n\n\n<p>The field lastname is concatenated to the string Mr.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Substring<\/h2>\n\n\n\n<p>The substr function extracts a substring from a string. Syntax:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>substr( string, start position, length)<\/code><\/pre>\n\n\n\n<p>where the first position is 1 and the length of the substring is optional. Some examples<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>substr('substring', 2)     -- ubstring\nsubstr('substring', 1, 3)  -- sub\nsubstr('substring', -2, 2) -- ng<\/code><\/pre>\n\n\n\n<p>This function works on Oracle 8i and above.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Limiting results<\/h2>\n\n\n\n<p>MySQL and Postgres support the limit clause which limits the number of entries returned when a query is executed:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>select * from member limit 5<\/code><\/pre>\n\n\n\n<p>This query will return at most 5 entries. To do the same in Oracle, you need to use the rownum clause:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>select * from member where rownum &lt; 6<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Comparing years in dates<\/h2>\n\n\n\n<p>Suppose you have two dates and you want to compares the years rather the exact date. Make sure you know the format of the dates. The use the following code:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>to_char(to_date(somedate, 'yyyymmdd'), 'YYYY') = to_char(to_date(somedate, 'yyyymmdd'), 'YYYY')<\/code><\/pre>\n\n\n\n<p>Change yyyymmdd to your date format.<\/p>\n\n\n\n<p>This example have been tested on Oracle 8i and above.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Extract parts of a date<\/h2>\n\n\n\n<p>Extract function is used to extract year, month, or date from a date or timezone hour or timezone minute from an interval. Examples:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>extract(DAY FROM DATE '2011-01-02')    -- 02\nextract(MONTH FROM DATE '2011-01-02')    -- 01\nextract(YEAR FROM DATE '2011-11-11')    -- 2003<\/code><\/pre>\n\n\n\n<p>extract function was introduced in Oracle 9i and above<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Testing for inequality<\/h2>\n\n\n\n<p>The following three operators have the identical meaning: !=, ^=, &lt;&gt;<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>select name from worker where gender &lt;> 'male';<\/code><\/pre>\n\n\n\n<p>Another way to test for inequality it to use &#8220;not in&#8221;<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>select name from worker where type not in ('manager','contractor');<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Changing case To change the first letter of the word to cap, use the inicap function. Use upper to covert to uppercase and lower to convert to lower case. Examples: These functions work on Oracle 8i and above. Concatenation Peculiarly, Oracle uses the double pipe ( || ) as a concatenation operator. Most programming languages [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[16,515],"tags":[14,3],"class_list":["post-30","post","type-post","status-publish","format-standard","hentry","category-database","category-oracle","tag-database","tag-oracle"],"_links":{"self":[{"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/posts\/30","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/comments?post=30"}],"version-history":[{"count":2,"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/posts\/30\/revisions"}],"predecessor-version":[{"id":32,"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/posts\/30\/revisions\/32"}],"wp:attachment":[{"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/media?parent=30"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/categories?post=30"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/tags?post=30"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}