CONVERSION FUNCTIONS
TO_CHAR (DATE)
- TO_CHAR(date, ‘format’)
- It’s case-sensitive and must be written between quotes.
- It must contain a valid date format.
- Let’s list the hire dates of employees like below.
SELECT hire_date, TO_CHAR (hire_date, 'dd.mm.yyyy') "dd.mm.yyyy", TO_CHAR (hire_date, 'DD Month YYYY') "DD Month YYYY", TO_CHAR (hire_date, 'Ddspth Month YYYY HH:MI:SS') "Ddspth Month YYYY HH:MI:SS " FROM hr.employees;
TO_NUMBER and TO_DATE
- TO_NUMBER(char [, ‘format’])
It converts characters to number format.
- TO_DATE(char [, ‘format’])
It converts characters to date format.
SELECT
TO_NUMBER ('2000'),
SYSDATE,
TO_DATE ('18/07/2014', 'dd.mm.yyyy'),
TO_DATE ('20140315', 'yyyymmdd'),
TO_DATE ('070910', 'MMDDYY')
FROM DUAL;
NESTED FUNCTIONS
- Functions can be called nested.
- The inner functions, runs first.
- Result returns from inner function to outer function
- Let’s write some nested functions.
SELECT first_name, last_name, UPPER (CONCAT (SUBSTR (LAST_NAME, 1, 6), '_US')) FROM hr.employees;
NVL FUNCTION
- It converts null value to real values
- It can be used on date, character and number data.
- NVL(expr1, expr2)
- Let’s list the salary, commision and annual salary of the employees, using NVL function.
SELECT last_name, salary, NVL (commission_pct, 0),(salary * 12) + (salary * 12 * NVL (commission_pct, 0)) AN_SAL FROM hr.employees;
DECODE FUNCTION
- Decode function allows you to perform IF-THEN-ELSE functionality in your queries.
- It’s similar to CASE statement.
- Let’s make 10% raise to ‘IT_PROG’ employees,
15% to ‘ST_CLERK’ emplyoyees and
20% to ‘SA_REP’ employees.
SELECT last_name, job_id, salary, DECODE (job_id, 'IT_PROG', 1.10 * salary, 'ST_CLERK', 1.15 * salary, 'SA_REP', 1.20 * salary, salary)"Raised Salary" FROM hr.employees ORDER BY 1
CASE STATEMENT
- It checks the WHEN block and if it’s true, executes the THEN block.
- Let’s write the previous example with ‘Case Statement’.
SELECT first_name, last_name, job_id, salary, CASE job_id WHEN 'IT_PROG' THEN 1.10 * salary WHEN 'ST_CLERK' THEN 1.15 * salary WHEN 'SA_REP' THEN 1.20 * salary ELSE salary END "Raised Salary" FROM hr.employees;
