Chapter 3 – Single Row Functions
- Charcter Functions
- Numeric Functions
- Date Functions
- Converting Functions
- NVL Function
- Decode Function
- CASE Statement
- SQL Functions can be classiifed under 2 parts as; Single-row Functions and Multiple-row functions.
SINGLE ROW FUNCTIONS
- function_name [(arg1, arg2,…)]
- 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';
- 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';