Oracle functions:
Single-Row Functions:
-Manipulates Data Items
-Accepts arguments and return one value
-Act on each row returned.
-Return one result per row
-May modify the data type
-can be nested.
-Accepts arguments which can be a column or an expression.
Syntax:
Function_name [(arg1,arg2,….)]
Single row functions are as below:
Character functions:
—-Case-manipulation functions
a. LOWER e.g [LOWER (‘QA COURSE’) => qa course]
b. UPPER e.g [UPPER (‘qa course’) =>QA COURSE]
c. INITCAP e.g [INITCAP(‘QA COURSE ’)=>Qa course]
—–Character-manipulation functions
a. CONCAT e.g [CONCAT (‘Hello’,’World’) =>HelloWorld]
b. SUBSTR e.g [SUBSTR (‘HelloWorld’,1,5) =>Hello ]
c. LENGTH e.g [LENGTH (‘HelloWorld’) =>10 ]
d. INSTR e.g [INSTR (‘HelloWorld’,’W’) =>6 ]
e. LPAD e.g [LPAD (salary,10,’*’) =>*****24000]
f. RPAD e.g [RPAD(salary,10,’*’) =>24000*****]
g. TRIM e.g [‘H’ FROM ‘HelloWorld’ =>elloworld]
h. REPLACE
EXAMPLE:
SELECT employee_id,CONCAT(first_name,last_name) NAME,job_id,LENGTH (last_name),INSTR (last_name,’a’) “Contains ‘a’?”
FROM employees
WHERE SUBSTR(job_id,4) =’REP’;
—–Number Functions
a. ROUND (45.926 ,2) =>45.93
b. TRUNC (45.926 ,2) =>45.92
c. MOD (1600,300) =>100
—–Date Functions
SYSDATE is a function that returns
–Date
–Time
-MONTHS_BETWEEN (’01-SEP-95’,’11-JAN-94’) => 19.6774194
-ADD_MONTHS (’11-JAN-94’,6) =>’11-JUL-94’
-NEXT_DAY (’01-SEP-95’,’FRIDAY’) =>’08-SEP-95’
-LAST_DAY (’01-FEB-95’) =>’28-FEB-95’
—-Conversion Functions:
a. Implicit data type conversion.
b. Explicit data type conversion.
Implicit data conversion of those Oracle server can automatically convert.
e.g
VARCHAR2 OR CHAR =>NUMBER
VARCHAR2 OR CHAR=>DATE
NUMBER =>VARCHAR2
DATE =>VARCHAR2
Explicit Data type Conversion occurred when opposite above.
e.g
SELECT last_name,TO_CHAR(hire_date,’fmDD Month YYYY’) as HIREDATE
From empoyees;
General Functions:
–NVL
–NVL2
–NULLIF
–COALESCE
I will discuss details in my next post
