Oracle Built-in Functions - Part 1

1- Date Functions

a) ADD_MONTHS; Add Month(s) To A Date

SELECT SYSDATE today, add_months(SYSDATE, 2) "two-months-later" FROM dual ;

TODAY      two-months
---------- ----------
31/12/2006 28/02/2007

SELECT SYSDATE today, add_months(SYSDATE, -2) "two-months-before" FROM dual ;

TODAY      two-months
---------- ----------
31/12/2006 31/10/2006

b) MONTHS_BETWEEN; Returns The Months Separating Two Dates – MONTHS_BETWEEN(, )

-- how-many-months-between-100-days-before-and-200-days-after
SELECT MONTHS_BETWEEN(SYSDATE+200, SYSDATE-100) MONTHS_BETWEEN FROM dual;

MONTHS_BETWEEN
--------------
9,90322581

-- changed the signs
SELECT MONTHS_BETWEEN(SYSDATE-200, SYSDATE+100) MONTHS_BETWEEN FROM dual;

MONTHS_BETWEEN
--------------
-9,8709677

c) EXTRACT; returns the value of a specified datetime field from a datetime or interval value expression. Also can be used for XMLType data,

EXTRACT(XML) is similar to the EXISTSNODE function.

SELECT EXTRACT(YEAR FROM SYSDATE) year, EXTRACT(MONTH FROM SYSDATE) month FROM DUAL;

YEAR      MONTH
---------- ----------
2006         12

2- Case and Decode Functions

WITH airplanes AS (
SELECT object_id line_number,
owner customer
FROM all_objects
WHERE owner = 'SYS' and ROWNUM < 3
UNION ALL
SELECT object_id line_number,
owner customer
FROM all_objects
WHERE owner = 'HR' and ROWNUM < 3
UNION ALL
SELECT object_id line_number,
owner customer
FROM all_objects
WHERE owner = 'SYSTEM' and ROWNUM < 3
UNION ALL
SELECT object_id line_number,
owner customer
FROM all_objects
WHERE object_id > 1000 and ROWNUM < 3
UNION ALL
SELECT object_id line_number,
owner customer
FROM all_objects
WHERE object_id > 10000 and ROWNUM < 3
UNION ALL
SELECT 150000 line_number,
'TONG' customer
FROM dual )
SELECT line_number,
CASE WHEN (line_number BETWEEN 1 AND 1000) THEN 'Part-One'
WHEN (line_number BETWEEN 1001 AND 10000) THEN 'Part-Two'
ELSE 'Other'
END AS Part_Info, customer,
DECODE(customer,
'SYS', 'American Airlines',
'HR', 'Intl. Leasing Corp.',
'SYSTEM', 'Northwest Orient',
'Southwest Airlines') AIRLINE
FROM airplanes ;

LINE_NUMBER PART_INF CUSTOMER                       AIRLINE
----------- -------- ------------------------------ -------------------
20 Part-One SYS                            American Airlines
44 Part-One SYS                            American Airlines
12088 Other    HR                             Intl. Leasing Corp.
12089 Other    HR                             Intl. Leasing Corp.
2563 Part-Two SYSTEM                         Northwest Orient
2565 Part-Two SYSTEM                         Northwest Orient
1002 Part-Two SYS                            American Airlines
1004 Part-Two SYS                            American Airlines
10086 Other    SYS                            American Airlines
10136 Other    SYS                            American Airlines
150000 Other    TONG                           Southwest Airlines

11 rows selected.

3- NULL functions

DROP TABLE t PURGE ;
CREATE TABLE t (
category  VARCHAR2(20),
outval    NUMBER(3),
inval     NUMBER(3));

INSERT INTO t VALUES ('Groceries', 10, NULL);
INSERT INTO t VALUES ('Payroll', NULL, 100);
INSERT INTO t VALUES ('Groceries', 20, NULL);
INSERT INTO t VALUES ('Payroll', NULL, 200);
INSERT INTO t VALUES ('Groceries', 30, NULL);

SELECT * FROM t;

CATEGORY                 OUTVAL      INVAL
-------------------- ---------- ----------
Groceries                    10
Payroll                                100
Groceries                    20
Payroll                                200
Groceries                    30

a) NVL, if the value is NULL returns given value

select category, nvl(outval, 0), inval fro

CATEGORY             NVL(OUTVAL,0)      INVAL
-------------------- ------------- ----------
Groceries                       10
Payroll                          0        100
Groceries                       20
Payroll                          0        200
Groceries                       30

b) NVL2, Returns First Value if NULL, Second Value if NOT NULL

SELECT category, NVL2(outval, -outval, inval)
FROM t;

CATEGORY             NVL2(OUTVAL,-OUTVAL,INVAL)
-------------------- --------------------------
Groceries                                   -10
Payroll                                     100
Groceries                                   -20
Payroll                                     200
Groceries                                   -30

c) COALESCE, Returns the first non-null value

DROP TABLE test PURGE ;
CREATE TABLE test (
col1  VARCHAR2(1),
col2  VARCHAR2(1),
col3  VARCHAR2(1));

INSERT INTO test VALUES (NULL, 'B', 'C');
INSERT INTO test VALUES ('A', NULL, 'C');
INSERT INTO test VALUES (NULL, NULL, 'C');
INSERT INTO test VALUES ('A', 'B', 'C');

SELECT * FROM test ;

C C C
- - -
B C
A   C
C
A B C

SELECT COALESCE(col1, col2, col3) FROM test;

C
-
B
A
C
A

4- String Functions

a) TRANSLATE, Character Replacement function

-- change "'" with "|"
SELECT TRANSLATE('comma,delimited,list', ',', '|') with_pipe FROM dual;

WITH_PIPE
--------------------
comma|delimited|list

-- In this demo a string is first encrypted then decrypted
SELECT TRANSLATE('this is a secret', 'abcdefghijklmnopqrstuvxyz', '0123456789qwertyuiop[kjhbv') encrypted , TRANSLATE('p78o 8o 0 o42i4p', '0123456789qwertyuiop[kjhbv', 'abcdefghijklmnopqrstuvxyz') decrypted FROM   dual;

ENCRYPTED        DECRYPTED
---------------- ----------------
p78o 8o 0 o42i4p this is a secret

-- turkish character example
SELECT TRANSLATE('Aç üş jı', 'ığüşöç', 'igusoc') all_english FROM dual;

ALL_ENGL
--------
Ac us ji

b) REVERSE, returns the reverse of the given string(but an un-supported function)

-- finding palidromes example
CREATE TABLE palidrome_test ( text VARCHAR2(100) );

INSERT ALL
INTO palidrome_test VALUES (’Eat More Bananas’)
INTO palidrome_test VALUES (’A Toyota’’s a Toyota’)
INTO palidrome_test VALUES (’Never odd or even.’)
INTO palidrome_test VALUES (’Some men interpret nine memos.’)
INTO palidrome_test VALUES (’Palindromes are the enemy of productivity’)
SELECT * FROM dual;

SELECT rownum, CASE
WHEN text_filtered = REVERSE(text_filtered)
THEN ‘Yes’
ELSE ‘No’
END AS palindrome
FROM   ( SELECT text, UPPER(REPLACE(TRANSLATE(text,’ ‘’,.:;!?”‘,’ ‘),’ ‘)) AS text_filtered FROM   palidrome_test );

ROWNUM PAL
---------- ---
1 No
2 Yes
3 Yes
4 Yes
5 No

5- Numeric Functions

a) GREATEST and LEAST, Returns the largest and the smallest of multiple values

SELECT GREATEST(9, 67.6, 10) the_great, LEAST(9, 67.6, 10) the_small FROM dual;

THE_GREAT  THE_SMALL
---------- ----------
67,6          9

b) NANVL, Returns Alternate Number If The Value Is Not A Number

CREATE TABLE fpd (
dec_num    NUMBER(10,2),
bin_double BINARY_DOUBLE,
bin_float  BINARY_FLOAT);

INSERT INTO fpd VALUES (0, 'NaN', 'NaN');
COMMIT;

SELECT bin_double, NANVL(bin_double, 0) FROM fpd;

BIN_DOUBLE NANVL(BIN_DOUBLE,0)
---------- -------------------
Nan                   0

SELECT bin_float, NANVL(bin_float, 0) FROM fpd;

BIN_FLOAT NANVL(BIN_FLOAT,0)
---------- ------------------
Nan                  0

6- Other

a) SOUNDEX, Returns Character String Containing The Phonetic Representation Of Another String

SELECT CASE WHEN SOUNDEX('doych') = SOUNDEX('deutch') THEN ‘Match’
ELSE ‘No match’
END comparison1,
CASE WHEN SOUNDEX('pee-air') = SOUNDEX('pierre') THEN ‘Match’
ELSE ‘No match’
END comparison2
FROM DUAL;

COMPARIS COMPA
-------- -----
No match Match

b) VSIZE, returns the Byte Size

SELECT VSIZE('Tonguc') character, VSIZE(5) numb, VSIZE(SYSDATE) dat FROM dual;

CHARACTER       NUMB        DAT
---------- ---------- ----------
6          2          7

- DATE type stored as seven bytes; “century, year, month, day, hour, minute, second”,
- VARCHAR2 type stored as its length,
- NUMBER type uses variable-length scientific notation;
* Oracle stores a number in base 100 format.
* Each byte can store 2 digits.
* One byte is reserved for the exponent.
* When a negative number is stored, an additional byte is required for the sign (-).
* NUMBER(10) and NUMBER will take the same amount of space.
* They will also shrink to the minimum size required to store the contained data.
* The maximum precision for a number is NUMBER(38)

select vsize(1100) from dual;

VSIZE(1100)
-----------
2 /* 2 bytes : Note that the number is stored as (11*10exp2) Thus it requires 1 byte for the two digits (11) and one byte for the exponent(2) */

select vsize(-10.02) from dual;

VSIZE(-10.02)
————-
4 /* 4 bytes : One for sign, 2 for digits(1002) and one for exponent(-2) */

Testing Information : the scripts mentioned are tested on Oracle Database 10g Express Edition Release 10.2.0.1.0

References Used :
Complete grouped list of Oracle functions
Oracle® Database SQL Reference 10g Release 2 (10.2)

About these ads