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

Chapter 3 – Single Row Functions

  • Charcter Functions
  • Numeric Functions
  • Date Functions
  • Converting Functions
  • NVL Function
  • Decode Function
  • CASE Statement

 

SQL FUNCTIONS

  • SQL Functions can be classiifed under 2 parts as; Single-row Functions and Multiple-row functions.

 

SINGLE ROW FUNCTIONS

  • function_name [(arg1, arg2,…)]

 

CASE-MANIPULATIVE FUNCTIONS

  • Let’s list the employees name’s in uppercase, lowercase and first character in uppercase, rest is in lowercase.
SELECT first_name,

LOWER (first_name) "Lowercase",

UPPER (first_name) "Uppercase",

INITCAP (LOWER (first_name)) "First Letter"

FROM hr.employees;

 

 

  • Character functions can be used to find the correct result in WHERE statement, as in the example.
  • Let’s list the employees whose name is ‘steven’.No rows will return
SELECT first_name, last_name, job_id

FROM hr.employees

WHERE first_name = 'steven';

  • Let’s convert the name column to lowercase and list the employees whose name is ‘steven’.
SELECT first_name, last_name, job_id

FROM hr.employees

WHERE LOWER (first_name) = 'steven';

 

CHARACTER FUNCTIONS

  • Let’s list the name, surname of the employees and try some of the functions.
SELECT first_name,last_name,

CONCAT(first_name, last_name) "Full Name",

job_id,

LENGTH (last_name)"Length",

INSTR(last_name, 'a') "Where is'a'?",

LPAD(first_name,10,'*') "Left Padding",

RPAD(first_name,10,'-') "Right Padding",

REPLACE(first_name,'e','a')"Replace" ,

TRIM(' HELLO WORLD ') "Trim"

FROM hr.employees WHERE SUBSTR(job_id, 4) = 'REP';