Oracle SQL Tutorials – Chapter 3 (Part 3 of 3)

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;