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';