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

CONVERSION FUNCTIONS

Ä°lgili resim

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;

 

Leave a Reply

Your email address will not be published. Required fields are marked *