Commonly used code in Oracle

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:

initcap('change case')  -- Change Case
lower('Change Case')    -- change case
upper('change case')    -- CHANGE CASE

These functions work on Oracle 8i and above.

Concatenation

Peculiarly, Oracle uses the double pipe ( || ) as a concatenation operator. Most programming languages use || for the or operator. Example:

insert into mytable (id, name) values (12, 'Mr. ' || lastname);

The field lastname is concatenated to the string Mr.

Substring

The substr function extracts a substring from a string. Syntax:

substr( string, start position, length)

where the first position is 1 and the length of the substring is optional. Some examples

substr('substring', 2)     -- ubstring
substr('substring', 1, 3)  -- sub
substr('substring', -2, 2) -- ng

This function works on Oracle 8i and above.

Limiting results

MySQL and Postgres support the limit clause which limits the number of entries returned when a query is executed:

select * from member limit 5

This query will return at most 5 entries. To do the same in Oracle, you need to use the rownum clause:

select * from member where rownum < 6

Comparing years in dates

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:

to_char(to_date(somedate, 'yyyymmdd'), 'YYYY') = to_char(to_date(somedate, 'yyyymmdd'), 'YYYY')

Change yyyymmdd to your date format.

This example have been tested on Oracle 8i and above.

Extract parts of a date

Extract function is used to extract year, month, or date from a date or timezone hour or timezone minute from an interval. Examples:

extract(DAY FROM DATE '2011-01-02')    -- 02
extract(MONTH FROM DATE '2011-01-02')    -- 01
extract(YEAR FROM DATE '2011-11-11')    -- 2003

extract function was introduced in Oracle 9i and above

Testing for inequality

The following three operators have the identical meaning: !=, ^=, <>

select name from worker where gender <> 'male';

Another way to test for inequality it to use “not in”

select name from worker where type not in ('manager','contractor');