Analytics

Thursday, May 9, 2013

Oracle Numeric Functions

Numeric functions accept numeric input and return numeric values.

  • ABS
  • ACOS
  • ASIN
  • ATAN
  • ATAN2
  • BITAND
  • CEIL
  • COS
  • COSH
  • EXP
  • FLOOR
  • LN
  • LOG
  • MOD
  • NANVL
  • POWER
  • REMAINDER
  • ROUND (number)
  • SIGN
  • SIN
  • SINH
  • SQRT
  • TAN
  • TANH
  • TRUNC (number)
  • WIDTH_BUCKET

ABS Function

ABS Function returns the absolute value of a number.

Examples

SELECT ABS(-15) "Absolute" FROM DUAL;

  Absolute
----------
        15

abs(-23)  would return 23
abs(-23.6)  would return 23.6
abs(-23.65)  would return 23.65
abs(23.65)  would return 23.65
abs(23.65 * -1)  would return 23.65

ACOS Function

ACOS Function returns the arc cosine of a number. The argument number must be in the range of -1 to 1, and the function returns a value in the range of 0 to pi, expressed in radians.

Examples

SELECT ACOS(.3)"Arc_Cosine" FROM DUAL;

Arc_Cosine
----------
1.26610367

acos(0.2) would return 1.36943840600457
acos(0.35) would return 1.21322522314939
acos(-0.15) would return 1.72136459957158

Friday, April 19, 2013

Oracle Character Functions

A character function is a function that takes one or more character values as parameters and returns either a character value or a number value.

The Built-In Character Functions

  • ASCII
  • CHR
  • CONCAT
  • INITCAP
  • LOWER
  • LPAD
  • LTRIM
  • NLS_INITCAP
  • REGEXP_REPLACE
  • REGEXP_SUBSTR
  • REPLACE
  • RPAD
  • RTRIM
  • SOUNDEX
  • SUBSTR
  • TRANSLATE
  • TREAT
  • TRIM
  • UPPER

ASCII Function

Returns the ASCII code of a character.

Examples

SELECT ASCII('A') FROM DUAL;

ASCII('A')
----------
       65

SELECT ASCII('a') FROM DUAL;

ASCII('A')
----------
       97

SELECT ASCII('Z') FROM DUAL;

ASCII('Z')
----------
       90

CHR Function

Returns the character associated with the specified collating code.

The CHR function is the inverse of ASCII. It returns a VARCHAR2 character (length 1) that corresponds to the location in the collating sequence provided as a parameter.

Examples

SELECT CHR(65) FROM DUAL;

CHR(65)
-------
     A

SELECT CHR(97) FROM DUAL;

CHR(97)
-------
     a

SELECT CHR(67)||CHR(65)||CHR(84) "Cat" FROM DUAL;

Cat
---
CAT

CONCAT Function

Concatenates two strings into one.

The CONCAT function concatenates by taking two VARCHAR2 strings and returning those strings appended together in the order specified.

The CONCAT function is equivalent to the concatenation operator (||).

Examples

select CONCAT('abc', 'defg') from dual
 
CONCAT('abc', 'defg')
----------------------
abcdefg

SELECT CONCAT(FIRST_NAME, LAST_NAME) FULL_NAME, DEPARTMENT_ID 
  FROM EMPLOYEES;

FULL_NAME            DEPARTMENT_ID
------------------- -----------------
DonaldOConnell                  50
DouglasGrant                    50
JenniferWhalen                  10
MichaelHartstein                20
PatFay                          20

INITCAP Function

INITCAP function sets the first character in each word to uppercase and the rest to lowercase.

The return value is the same datatype as char.

Examples

SELECT INITCAP('the soap') "Initcap" FROM DUAL; 

Initcap
---------
The Soap

LOWER Function

LOWER function converts all letters in the specified string to lowercase. If there are characters in the string that are not letters, they are unaffected by this function.

Examples

SELECT LOWER('The Soap') "lower" FROM DUAL; 

lower
---------
the soap

SELECT LOWER('THE SOAP') "lower" FROM DUAL; 

lower
---------
the soap

LPAD Function

LPAD function pads the left-side of a string with a specific set of characters (when string1 is not null). This function is useful for formatting the output of a query.

Syntax

lpad( string1, padded_length, [ pad_string ] )

string1 is the string to pad characters to (the left-hand side).

padded_length is the number of characters to return. If the padded_length is smaller than the original string, the lpad function will truncate the string to the size of padded_length.

pad_string is optional. This is the string that will be padded to the left-hand side of string1. If this parameter is omitted, the lpad function will pad spaces to the left-side of string1.

Examples

SELECT LPAD('Oracle',10,'*') "LPAD example"   FROM DUAL;

LPAD example
---------------
  ****Oracle

SELECT LPAD('12345',10,'0') "LPAD example"   FROM DUAL;

LPAD example
---------------
  0000012345

LTRIM Function

In Oracle/PLSQL, the LTRIM Function removes all specified characters from the left-hand side of a string.

Syntax

ltrim( string1, [ trim_string ] )

string1 is the string to trim the characters from the left-hand side.

trim_string is the string that will be removed from the left-hand side of string1. If this parameter is omitted, the LTRIM Function will remove all leading spaces from string1.

Examples

select ltrim('123123Tech', '123') from dual

ltrim('123123Tech', '123')
--------------------------
Tech


SELECT product_name, LTRIM(product_name, 'Monitor ') "Short Name"
   FROM products
   WHERE product_name LIKE 'Monitor%';

PRODUCT_NAME         Short Name
-------------------- ---------------
Monitor 17/HR        17/HR
Monitor 17/HR/F      17/HR/F
Monitor 17/SD        17/SD
Monitor 19/SD        19/SD
Monitor 19/SD/M      19/SD/M


ltrim('000123', '0');           would return '123'
ltrim('123123Tech', '123');     would return 'Tech'
ltrim('123123Tech123', '123');  would return 'Tech123'

NLS_INITCAP Function

NLS_INITCAP Function returns string with the first letter of each word in uppercase using the special linguistic requirements of case conversions.

The following examples show how the linguistic sort sequence results in a different return value from the function.

Examples

SELECT NLS_INITCAP('ijsland') "InitCap" FROM DUAL;

InitCap
-------
Ijsland

SELECT NLS_INITCAP('ijsland', 'NLS_SORT = XDutch') "InitCap"
   FROM DUAL;

InitCap
-------
IJsland

REPLACE Function

REPLACE Function replaces a sequence of characters in a string with another set of characters.

REPLACE provides functionality related to that provided by the TRANSLATE function. TRANSLATE provides single-character, one-to-one substitution. REPLACE lets you substitute one string for another as well as to remove character strings.

Syntax

replace( string1, string_to_replace, [ replacement_string ] )

string1 is the string to replace a sequence of characters with another set of characters.

string_to_replace is the string that will be searched for in string1.

replacement_string is optional. All occurrences of string_to_replace will be replaced with replacement_string in string1. If the replacement_string parameter is omitted, the replace function simply removes all occurrences of string_to_replace, and returns the resulting string.

Examples

SELECT REPLACE('JACK and JUE','J','BL') "Changes" FROM DUAL;

Changes
--------------
BLACK and BLUE

replace('123123tech', '123');   would return 'tech'
replace('123tech123', '123');   would return 'tech'
replace('222tech', '2', '3');   would return '333tech'
replace('0000123', '0');        would return '123'
replace('0000123', '0', ' ');   would return '    123'

RPAD Function

RPAD Function pads the right-side of a string with a specific set of characters. This function is useful for formatting the output of a query.

Syntax

rpad( string1, padded_length, [ pad_string ] )

string1 is the string to pad characters to (the right-hand side), when string1 is not null.

padded_length is the number of characters to return. If the padded_length is smaller than the original string, the RPAD Function will truncate the string to the size of padded_length.

pad_string is optional. This is the string that will be padded to the right-hand side of string1. If this parameter is omitted, the RPAD Function will pad spaces to the right-side of string1.

Examples

rpad('tech', 7);   would return 'tech   '
rpad('tech', 2);   would return 'te'
rpad('tech', 8, '0');   would return 'tech0000'

RTRIM Function

RTRIM Function removes all specified characters from the right-hand side of a string.

Syntax

rtrim( string1, [ trim_string ] )

string1 is the string to trim the characters from the right-hand side.

trim_string is the string that will be removed from the right-hand side of string1. If this parameter is omitted, the RTRIM Function will remove all trailing spaces from string1.

Examples

SELECT RTRIM('BROWNING: ./=./=./=./=./=.=','/=.') "RTRIM example" 
FROM DUAL;
 
RTRIM exam
----------
BROWNING:

SOUNDEX Function

SOUNDEX Function lets you compare words that are spelled differently, but sound alike in English. This function returns a phonetic representation (the way it sounds) of a string.

SOUNDEX Algorithm

  1. The soundex return value will always begin with the first letter of string1 and remove all other occurrences of the following letters: a, e, h, i, o, u, w, y.
  2. Assign numbers to the remaining letters (after the first) as follows:
  3. b, f, p, v = 1
    c, g, j, k, q, s, x, z = 2
    d, t = 3
    l = 4
    m, n = 5
    r = 6
    
  4. The soundex function is not case-sensitive. What this means is that both uppercase and lowercase characters will generate the same soundex return value.
  5. Return the first four bytes padded with 0.

Examples

SELECT last_name, first_name
     FROM hr.employees
     WHERE SOUNDEX(last_name) = SOUNDEX('SMYTHE');

LAST_NAME  FIRST_NAME
---------- ----------
Smith      Lindsey
Smith      William

soundex('tech on the net'); would return 'T253'
soundex('TECH ON THE NET'); would return 'T253'
soundex('apples');         would return 'A142'
soundex('apples are great'); would return 'A142'
soundex('applus');         would return 'A142'

SUBSTR Function

SUBSTR Function allows you to extract a substring from a string.

Syntax

substr( string, start_position, [ substring_length ] )

string is the source string.

start_position is the position for extraction. The first position in the string is always 1.

substring_length is optional. It is the number of characters to extract. If this parameter is omitted, substr will return the entire string.

  • If start_position is 0, then substr treats start_position as 1 (ie: the first position in the string).
  • If start_position is a positive number, then substr starts from the beginning of the string.
  • If start_position is a negative number, then substr starts from the end of the string and counts backwards.
  • If substring_length is a negative number, then substr will return a NULL value.

Examples

SELECT SUBSTR('ABCDEFG',3,4) "Substring"
     FROM DUAL;
 
Substring
---------
CDEF

SELECT SUBSTR('ABCDEFG',-5,4) "Substring"
     FROM DUAL;

Substring
---------
CDEF

TRANSLATE Function

TRANSLATE Function replaces a sequence of characters in a string with another set of characters. However, it replaces a single character at a time. For example, it will replace the 1st character in the string_to_replace with the 1st character in the replacement_string. Then it will replace the 2nd character in the string_to_replace with the 2nd character in the replacement_string, and so on.

Syntax

translate( string1, string_to_replace, replacement_string )

string1 is the string to replace a sequence of characters with another set of characters.

string_to_replace is the string that will be searched for in string1.

replacement_string - All characters in the string_to_replace will be replaced with the corresponding character in the replacement_string.

Examples

SELECT TRANSLATE('SQL*Plus User''s Guide', ' */''', '___') FROM DUAL;

TRANSLATE('SQL*PLUSU
--------------------
SQL_Plus_Users_Guide

TREAT Function

The Oracle TREAT function allows you to change the declared type of the expression used in TREAT. This function comes in handy when you have a subtype that is more specific to your data and you want to convert the parent type to the more specific one.

Examples

CREATE TYPE PERSON AS OBJECT (
FIRST_NAME VARCHAR2(15),
LAST_NAME VARCHAR2(15),
SALARY NUMBER(10));

CREATE TABLE PERSON_TABLE
(
ID      NUMBER(4) PRIMARY KEY,
PERSON_INFO  PERSON,
CREATED_DATE DATE
);

INSERT INTO PERSON_TABLE
VALUES(1,PERSON('SMITH','BLAKE',800),SYSDATE);

INSERT INTO PERSON_TABLE
VALUES(2,PERSON('ALLEN','WARD',800),SYSDATE);

SELECT * FROM PERSON_TABLE

ID   PERSON_INFO             CREATED_DATE
---  --------------------    --------------
1    (SMITH; BLAKE; 800)     06/05/2013 
2    (ALLEN; WARD; 900)      06/05/2013 

 SELECT ID,TREAT(PERSON_INFO AS PERSON).FIRST_NAME FIRST_NAME,
        TREAT(PERSON_INFO AS PERSON).LAST_NAME LAST_NAME,
        TREAT(PERSON_INFO AS PERSON).SALARY SALARY,
        CREATED_DATE 
  FROM PERSON_TABLE 
  
   ID   FIRST_NAME    LAST_NAME     SALARY   CREATED_DATE
-----   ------------  ------------  -------  ------------
    1   SMITH         BLAKE            800    06/05/2013
    2   ALLEN         WARD             900    06/05/2013

TRIM Function

TRIM Function enables you to remove leading or trailing characters (or both) from a character string.

Syntax

trim( [ leading | trailing | both [ trim_character ] string1 )
  • LEADING- removes any leading characters equal to trim_character.
  • TRAILING- removes any trailing characters equal to trim_character.
  • BOTH- removes leading and trailing characters equal to trim_character.
  • If trim_character parameter is omitted, TRIM Function will remove all leading and trailing spaces from string1. string1 is the string to trim.
  • If you do not specify a value for the first parameter (leading, trailing, both), TRIM Function will remove trim_character from both the front and end of string1.

Examples

trim('   tech   ')                would return 'tech'
trim(' '  from  '   tech   ')     would return 'tech'
trim(leading '0' from '000123')   would return '123'
trim(trailing '1' from 'Tech1')   would return 'Tech'
trim(both '1' from '123Tech111')  would return '23Tech'

UPPER Function

UPPER Function converts all letters in the specified string to uppercase. If there are characters in the string that are not letters, they are unaffected by this function.

Examples

SELECT UPPER(last_name) "Uppercase"
   FROM employees;

upper('Tech on the Net'); would return 'TECH ON THE NET'
upper('george burns 123   '); would return 'GEORGE BURNS 123   '

Sunday, April 14, 2013

Oracle Date and Time Functions

Date and Time functions operate on date (DATE), timestamp (TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE), and interval (INTERVAL DAY TO SECOND, INTERVAL YEAR TO MONTH) values.

Some of the datetime functions were designed for the Oracle DATE datatype (ADD_MONTHS, CURRENT_DATE, LAST_DAY, NEW_TIME, and NEXT_DAY). If you provide a timestamp value as their argument, Oracle Database internally converts the input type to a DATE value and returns a DATE value. The exceptions are the MONTHS_BETWEEN function, which returns a number, and the ROUND and TRUNC functions, which do not accept timestamp or interval values at all.

The remaining datetime functions were designed to accept any of the three types of data (date, timestamp, and interval) and to return a value of one of these types.

The datetime functions are:

  • ADD_MONTHS
  • CURRENT_DATE
  • CURRENT_TIMESTAMP
  • DBTIMEZONE
  • EXTRACT (datetime)
  • FROM_TZ
  • LAST_DAY
  • LOCALTIMESTAMP
  • MONTHS_BETWEEN
  • NEW_TIME
  • NEXT_DAY
  • ROUND (date)
  • SESSIONTIMEZONE
  • SYSDATE
  • SYSTIMESTAMP
  • TO_CHAR (datetime)
  • TO_TIMESTAMP
  • TO_TIMESTAMP_TZ
  • TO_DSINTERVAL
  • TO_YMINTERVAL
  • TRUNC (date)

ADD_MONTHS

ADD_MONTHS returns the date date plus integer months. The date argument can be a datetime value or any value that can be implicitly converted to DATE. The integer argument can be an integer or any value that can be implicitly converted to an integer. The return type is always DATE, regardless of the datatype of date. If date is the last day of the month or if the resulting month has fewer days than the day component of date, then the result is the last day of the resulting month. Otherwise, the result has the same day component as date.

Examples

SELECT TO_CHAR(ADD_MONTHS(hire_date,1),'DD-MON-YYYY') "Next month"
     FROM employees 
     WHERE last_name = 'Baer';

Next Month
-----------
07-JUL-2012

add_months('01-Aug-12', 3) would return '01-Nov-12'
add_months('01-Aug-12', -3) would return '01-May-12'

CURRENT_DATE

CURRENT_DATE returns the current date in the session time zone, in a value in the Gregorian calendar of datatype DATE.

Examples

SELECT SESSIONTIMEZONE, CURRENT_DATE FROM DUAL;

SESSIONTIMEZONE CURRENT_DATE
--------------- --------------------
+04:00          15/04/2013 09:13:26

CURRENT_TIMESTAMP

CURRENT_TIMESTAMP returns the current date and time in the session time zone, in a value of datatype TIMESTAMP WITH TIME ZONE. The difference between CURRENT_TIMESTAMP and LOCALTIMESTAMP is that CURRENT_TIMESTAMP returns a TIMESTAMP WITH TIME ZONE value while LOCALTIMESTAMP returns a TIMESTAMP value.

Examples

SELECT SESSIONTIMEZONE, CURRENT_TIMESTAMP FROM DUAL;

SESSIONTIMEZONE CURRENT_TIMESTAMP
--------------- ---------------------------------------------
+04:00          15/04/2013 09:20:22.333000 AM +04:00

DBTIMEZONE

DBTIMEZONE returns the value of the database time zone. The return type is a time zone offset (a character type in the format '[+|-]TZH:TZM') or a time zone region name, depending on how the user specified the database time zone value in the most recent CREATE DATABASE or ALTER DATABASE statement.

Examples

SELECT DBTIMEZONE FROM DUAL;

DBTIMEZONE
------
+00:00

EXTRACT (datetime)

EXTRACT extracts and returns the value of a specified datetime field from a datetime or interval value expression. When you extract a TIMEZONE_REGION or TIMEZONE_ABBR (abbreviation), the value returned is a string containing the appropriate time zone name or abbreviation. When you extract any of the other values, the value returned is in the Gregorian calendar. When extracting from a datetime with a time zone value, the value returned is in UTC. For a listing of time zone names and their corresponding abbreviations, query the V$TIMEZONE_NAMES dynamic performance view.

Examples

SELECT EXTRACT(month FROM order_date) "Month",
  COUNT(order_date) "No. of Orders"
  FROM orders
  GROUP BY EXTRACT(month FROM order_date)
  ORDER BY "No. of Orders" DESC;

     Month No. of Orders
---------- -------------
        11            15
         7            14
         6            14
         3            11
         5            10
         9             9
         2             9
         8             7
        10             6
         1             5
        12             4
         4             1
 
12 rows selected.

SELECT EXTRACT(YEAR FROM DATE '2013-04-10') FROM DUAL;

EXTRACT(YEARFROMDATE'1998-03-07')
---------------------------------
                             2013


SELECT EXTRACT(MONTH FROM DATE '2013-04-10') FROM DUAL;

EXTRACT(MONTHFROMDATE'1998-03-07')
---------------------------------
                               04

SELECT EXTRACT(DAY FROM DATE '2013-04-10') FROM DUAL;

EXTRACT(DAYFROMDATE'1998-03-07')
--------------------------------
                              10

FROM_TZ

FROM_TZ converts a timestamp value and a time zone to a TIMESTAMP WITH TIME ZONE value. time_zone_value is a character string in the format 'TZH:TZM' or a character expression that returns a string in TZR with optional TZD format.

Examples

SELECT FROM_TZ(TIMESTAMP '2013-03-28 08:00:00', '4:00') FROM DUAL;

FROM_TZ(TIMESTAMP'2013-03-2808:00:00','4:00')
-------------------------------------------------------
28-MAR-13 08.00.00 AM +04:00

LAST_DAY

LAST_DAY returns the date of the last day of the month that contains date. The return type is always DATE.

Examples

SELECT SYSDATE,
   LAST_DAY(SYSDATE) "Last",
   LAST_DAY(SYSDATE) - SYSDATE "Days Left"
   FROM DUAL;
 
SYSDATE   Last       Days Left
--------- --------- ----------
30-MAR-13 31-MAR-13          1

LOCALTIMESTAMP

LOCALTIMESTAMP returns the current date and time in the session time zone in a value of datatype TIMESTAMP. The difference between LOCALTIMESTAMP and CURRENT_TIMESTAMP is that LOCALTIMESTAMP returns a TIMESTAMP value while CURRENT_TIMESTAMP returns a TIMESTAMP WITH TIME ZONE value.

Examples

SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP FROM DUAL;

CURRENT_TIMESTAMP                    LOCALTIMESTAMP
-------------------------------------------------------------------
15/04/2013 10:45:01.535000 AM +04:00  15/04/2013 10:45:01.535000 AM

MONTHS_BETWEEN

MONTHS_BETWEEN returns number of months between dates date1 and date2. If date1 is later than date2, then the result is positive. If date1 is earlier than date2, then the result is negative. If date1 and date2 are either the same days of the month or both last days of months, then the result is always an integer. Otherwise Oracle Database calculates the fractional portion of the result based on a 31-day month and considers the difference in time components date1 and date2.

Examples

SELECT MONTHS_BETWEEN 
   (TO_DATE('03-03-2013','MM-DD-YYYY'),
    TO_DATE('01-01-2013','MM-DD-YYYY') ) "Months"
    FROM DUAL;

    Months
----------
2.06451612

NEW_TIME

NEW_TIME returns the date and time in time zone timezone2 when date and time in time zone timezone1 are date. The return type is always DATE, regardless of the datatype of date.

Examples

SELECT 
new_time (to_date ('2013/02/15 01:45', 'yyyy/mm/dd HH24:MI'), 'AST', 'MST') "New Date and Time"
FROM DUAL

New Date and Time
--------------------
14/02/2013 10:45:00 AM

NEXT_DAY

NEXT_DAY returns the date of the first weekday named by char that is later than the date date. The return type is always DATE.

Examples

SELECT NEXT_DAY('02-FEB-2013','TUESDAY') "NEXT DAY" FROM DUAL;

NEXT DAY
-----------
05-FEB-2013

ROUND (date)

ROUND returns date rounded to the unit specified by the format model fmt. The value returned is always of datatype DATE, even if you specify a different datetime datatype for date. If you omit fmt, then date is rounded to the nearest day. The date expression must resolve to a DATE value.

Examples

SELECT ROUND (TO_DATE ('27-07-12','DD-MM-YY'),'YEAR')  "New Year" FROM DUAL;
 
New Year
---------
01-01-13

SELECT ROUND (TO_DATE ('20-07-12','DD-MM-YY'),'MONTH')  "New Month" FROM DUAL;
 
New Month
---------
01-08-12

ROUND Function Valid Format Parameters:

Unit Valid Format Parameters Rounding Rule
Year SYYYY, YYYY, YEAR, SYEAR, YYY, YY, Y Rounds up on July 1st
Quarter Q Rounds up on the 16th day of the second month of the quarter
Month MONTH, MON, MM, RM Rounds up on the 16th day of the month
Week WW Same day of the week as the first day of the year
W W Same day of the week as the first day of the month
Day DDD, DD, J
Start day of the week DAY, DY, D
Hour HH, HH12, HH24
Minute MI

SESSIONTIMEZONE

SESSIONTIMEZONE returns the time zone of the current session.

Examples

SELECT SESSIONTIMEZONE FROM DUAL;

SESSION
-------
+04:00

SYSDATE

SYSDATE returns the current date and time set for the operating system on which the database resides. The datatype of the returned value is DATE.

Examples

SELECT TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS') "NOW"  FROM DUAL;

NOW
-------------------
04-16-2013 12:31:27

SYSTIMESTAMP

SYSTIMESTAMP returns the system date, including fractional seconds and time zone, of the system on which the database resides. The return type is TIMESTAMP WITH TIME ZONE.

Examples

SELECT SYSTIMESTAMP FROM DUAL;

SYSTIMESTAMP
-----------------------------------------------
16/04/2013 12:33:54.235000 PM +04:00

SELECT TO_CHAR(SYSTIMESTAMP, 'SSSSS.FF') "Fractional Seconds" FROM DUAL;

Fractional Seconds
---------------
45345.500000

TO_CHAR (datetime)

TO_CHAR (datetime) converts a datetime or interval value of DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, or TIMESTAMP WITH LOCAL TIME ZONE datatype to a value of VARCHAR2 datatype in the format specified by the date format fmt.

Examples

to_char(sysdate, 'yyyy/mm/dd'); would return '2003/07/09'
to_char(sysdate, 'Month DD, YYYY'); would return 'July 09, 2003'
to_char(sysdate, 'FMMonth DD, YYYY'); would return 'July 9, 2003'
to_char(sysdate, 'MON DDth, YYYY'); would return 'JUL 09TH, 2003'
to_char(sysdate, 'FMMON DDth, YYYY'); would return 'JUL 9TH, 2003'
to_char(sysdate, 'FMMon ddth, YYYY'); would return 'Jul 9th, 2003'

List of Valid Parameters For the TO_CHAR (datetime) Function

Parameter Explanation
YEAR Year, spelled out
YYYY 4-digit year
YYY, YY, Y Last 3, 2, or 1 digit(s) of year.
IYY, IY, I Last 3, 2, or 1 digit(s) of ISO year.
IYYY 4-digit year based on the ISO standard
Q Quarter of year (1, 2, 3, 4; JAN-MAR = 1).
MM Month (01-12; JAN = 01).
MON Abbreviated name of month.
MONTH Name of month, padded with blanks to length of 9 characters.
RM Roman numeral month (I-XII; JAN = I).
WW Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.
W Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh.
IW Week of year (1-52 or 1-53) based on the ISO standard.
D Day of week (1-7).
DAY Name of day.
DD Day of month (1-31).
DDD Day of year (1-366).
DY Abbreviated name of day.
J Julian day; the number of days since January 1, 4712 BC.
HH Hour of day (1-12).
HH12 Hour of day (1-12).
HH24 Hour of day (0-23).
MI Minute (0-59).
SS Second (0-59).
SSSSS Seconds past midnight (0-86399).
FF Fractional seconds.

TO_TIMESTAMP

TO_TIMESTAMP converts char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to a value of TIMESTAMP datatype.

Examples

SELECT TO_TIMESTAMP ('10-09-2012 14:10:10.123000', 'DD-MM-RR HH24:MI:SS.FF')
   FROM DUAL;

TO_TIMESTAMP('10-09-0214:10:10.123000','DD-MM-RRHH24:MI:SS.FF')
-------------------------------------------------------------------
10/09/2012 02:10:10.123000000 PM

TO_TIMESTAMP_TZ

TO_TIMESTAMP_TZ converts char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to a value of TIMESTAMP WITH TIME ZONE datatype.

SELECT TO_TIMESTAMP_TZ('2012-12-01 11:00:00 +4:00',
   'YYYY-MM-DD HH:MI:SS TZH:TZM') FROM DUAL;

TO_TIMESTAMP_TZ('2012-12-0111:00:00+04:00','YYYY-MM-DDHH:MI:SSTZH:TZM')
--------------------------------------------------------------------
01/12/2012 11:00:00.000000000 AM +04:00

TO_DSINTERVAL

TO_DSINTERVAL converts a character string of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to an INTERVAL DAY TO SECOND value.

Examples

SELECT employee_id, last_name FROM employees
   WHERE hire_date + TO_DSINTERVAL('100 10:00:00')
   <= DATE '2012-01-01';

EMPLOYEE_ID LAST_NAME
----------- ---------------
        100 King
        101 Kochhar
        200 Whalen

TO_YMINTERVAL

TO_YMINTERVAL converts a character string of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to an INTERVAL YEAR TO MONTH type, where char is the character string to be converted.

Examples

SELECT hire_date, hire_date + TO_YMINTERVAL('01-02') "14 months"
   FROM employees;

HIRE_DATE       14 months
---------       ---------
11/01/2013 11/03/2014
03/01/2013 03/03/2014
10/01/2013 10/03/2014
11/01/2013 11/03/2014
20/12/2012 20/02/2014
20/12/2012 20/02/2014
02/01/2013 02/03/2014

TRUNC (date)

The TRUNC (date) function returns date with the time portion of the day truncated to the unit specified by the format model fmt. The value returned is always of datatype DATE.

Examples

SELECT TRUNC(TO_DATE('27-10-2012','DD-MM-YY'), 'YEAR') "Trunc Year" FROM DUAL;
 
Trunc Year
---------
01/01/2012

SELECT TRUNC(TO_DATE('27-10-2012','DD-MM-YY'), 'MONTH') "Trunc Month" FROM DUAL;
 
Trunc Month
---------
01/10/2012

For Trunc (date) Function Valid Format Parameters

Unit Valid format parameters
Year SYYYY, YYYY, YEAR, SYEAR, YYY, YY, Y
ISO Year IYYY, IY, I
Quarter Q
Month MONTH, MON, MM, RM
Week WW
IW IW
W W
Day DDD, DD, J
Start day of the week DAY, DY, D
Hour HH, HH12, HH24
Minute MI

Tuesday, April 2, 2013

Oracle OPERATORS

Oracle provides operators to be used with data in order to perform some related action and return a result. An operator can act on a single operand (Unary Operators) or on two operands (Binary Operators).

The data items are called operands or arguments. Operators are represented by special characters or by keywords. For example, the multiplication operator is represented by an asterisk (*) and the operator that tests for nulls is represented by the keywords IS NULL.

Oracle provides following kinds of operators:

  1. Arithmetic Operators
  2. Concatenation Operators
  3. Comparison Operators
  4. Logical Operators
  5. Hierarchical Operators
  6. Set Operators

Arithmetic Operators

You can use an arithmetic operator in an expression to negate, add, subtract, multiply, and divide numeric values. The result of the operation is also a numeric value.

The available Arithmetic Operators are: +, -, *, and /.

Examples

The following are some examples.

SELECT sal + comm FROM emp
  WHERE SYSDATE - hiredate
  > 365;

UPDATE emp
  SET sal = sal * 1.1;

c := a + b;

Concatenation Operator

Concatenation Operator operate on two string operands to join them into a single string.

The available Concatenation Operator is ||.

Examples

SELECT 'Name is ' || ename
   FROM emp;

SELECT col1||col2||col3||col4 "Concatenation"
        FROM tab1;

Comparison Operators

Comparison Operators are used to build a comparable condition between two terms, thereby returning a logical result as TRUE, FALSE, or NULL.

The available Comparison operators are: =, <, >, <=, >=, (NOT)IN, (NOT) LIKE, (NOT) BETWEEN, (NOT) EXISTS, and IS (NOT) NULL.

Examples

The following are some examples.

= Equality test.
SELECT *  FROM emp
  WHERE sal = 1500;
> Greater than test
SELECT * FROM emp
  WHERE sal > 1500;
<= Less than or equal to tests
SELECT * FROM emp
  WHERE sal <= 1500;
NOT IN Evaluates to FALSE if any member of the set is NULL.
SELECT * FROM emp
  WHERE job NOT IN ('CLERK', 'ANALYST');
EXISTS TRUE if a subquery returns at least one row.
SELECT ename, deptno FROM dept
  WHERE EXISTS
  (SELECT * FROM emp
    WHERE dept.deptno = emp.deptno);

Logical Operators

Logical Operators operate on two operands and return a BOOLEAN output.

The available logical operators are: AND, OR, and NOT.

AND Operator

Returns TRUE if both component conditions are TRUE. Returns FALSE if either is FALSE. Otherwise returns UNKNOWN.

SELECT * FROM emp
  WHERE job = 'CLERK'
  AND deptno = 10;

OR Operator

Returns TRUE if either component condition is TRUE. Returns FALSE if both are FALSE. Otherwise returns UNKNOWN.

SELECT * FROM emp
  WHERE job = 'CLERK'
  OR deptno = 10;

NOT Operator

Returns TRUE if the following condition is FALSE. Returns FALSE if it is TRUE. If it is UNKNOWN, it remains UNKNOWN.

SELECT * FROM emp
  WHERE NOT (sal BETWEEN 1000 AND 2000);

Hierarchical Operators

Hierarchical Operators are used only in hierarchical queries.

The available Hierarchical Operators are: PRIOR and CONNECT_BY_ROOT.

Example

SELECT Lpad(ename,Length(ename) + LEVEL * 10 - 10,'-') 
FROM   emp 
START WITH mgr IS NULL 
CONNECT BY PRIOR empno = mgr;

KING
----------JONES
--------------------SCOTT
------------------------------ADAMS
--------------------FORD
------------------------------SMITH
----------BLAKE
--------------------ALLEN
--------------------WARD
--------------------MARTIN
--------------------TURNER
--------------------JAMES
----------CLARK
--------------------MILLER

Set Operators

Set Operators are used to compound multiple queries and return the combined result set.

The available SET Operators are: UNION, UNION ALL, MINUS, and INTERSECT.

UNION Example

The following statement combines the results with the UNION operator, which eliminates duplicate selected rows. This statement shows that you must match datatype (using the TO_DATE and TO_NUMBER functions) when columns do not exist in one or the other table:

SELECT part, partnum, to_date(null) date_in
    FROM orders_list1
UNION
SELECT part, to_number(null), date_in
    FROM orders_list2;

UNION ALL Example

The following statement combines the results with the UNION ALL operator, which does not eliminate duplicate selected rows:

SELECT part 
    FROM orders_list1 
UNION ALL 
SELECT part 
    FROM orders_list2;

PART 
---------- 
SPARKPLUG 
FUEL PUMP 
FUEL PUMP 
TAILPIPE 
CRANKSHAFT 
TAILPIPE 
TAILPIPE 

MINUS Example

The following statement combines results with the MINUS operator, which returns only rows returned by the first query but not by the second:

SELECT part FROM orders_list1 
MINUS 
SELECT part FROM orders_list2;

PART 
---------- 
SPARKPLUG 
FUEL PUMP 

INTERSECT Example

The following statement combines the results with the INTERSECT operator, which returns only those rows returned by both queries:

SELECT part FROM orders_list1 
INTERSECT 
SELECT part FROM orders_list2;

PART 
---------- 
TAILPIPE