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.
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.
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.
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');