SQL Functions

There are two types of SQL Functions:

1. Single-row functions
2. Multiple-row functions

Single row functions:
• Manipulate data items
• Accept arguments and return one value
• Act on each row returned
• Return one result per row
• May modify the data type
• Can be nested
• Accept arguments which can be a column or an expression

Types of Single row functions:

1. Character Functions
2. Number Functions
3. General Functions
4. Conversion Functions
5. Date Functions

1. Character Functions:
Character Functions is also two types:

• Case-manipulation functions (LOWER, UPPER, INITCAP)
• Character-manipulation functions (CONCAT,  SUBSTR, LENGTH, INSTR, LPAD | RPAD, TRIM, REPLACE)

examples:

SELECT employee_id, last_name, department_id
FROM employees
WHERE LOWER(last_name) = ‘higgins’;

CONCAT(‘Hello’, ‘World’)
SUBSTR(‘HelloWorld’,1,5)
LENGTH(‘HelloWorld’)
INSTR(‘HelloWorld’, ‘W’)
LPAD(salary,10,’*')
RPAD(salary, 10, ‘*’)
TRIM(‘H’ FROM ‘HelloWorld’)

SELECT employee_id, CONCAT(first_name, last_name) NAME,
job_id, LENGTH (last_name),
INSTR(last_name, ‘a’) “Contains ‘a’?”
FROM employees
WHERE SUBSTR(job_id, 4) = ‘REP’;

SELECT employee_id, CONCAT(first_name, last_name) NAME,
job_id, LENGTH (last_name),
INSTR(last_name, ‘a’) “Contains ‘a’?”
FROM employees
WHERE SUBSTR(job_id, 4) = ‘REP’;

2. Number Functions:

• ROUND: Rounds value to specified decimal ( ROUND(45.926, 2) 45.93)
• TRUNC: Truncates value to specified decimal (TRUNC(45.926, 2) 45.92)
• MOD: Returns remainder of division (MOD(1600, 300) 100)

Examples:

SELECT ROUND(45.923,2), ROUND(45.923,0),
ROUND(45.923,-1)
FROM DUAL;

SELECT TRUNC(45.923,2), TRUNC(45.923),
TRUNC(45.923,-2)
FROM DUAL;

SELECT last_name, salary, MOD(salary, 5000)
FROM employees
WHERE job_id = ‘SA_REP’;

3. Date Functions:

•  Oracle database stores dates in an internal numeric format: century, year, month, day, hours, minutes, seconds.
•  The default date display format is DD-MON-RR.
– Allows you to store 21st century dates in the 20th century by specifying only the last two digits of the year.
– Allows you to store 20th century dates in the 21st century in the same way.
•  SYSDATE is a function that returns: Date and Time
•  Arithmetic with Dates
– Add or subtract a number to or from a date for a resultant date value.
– Subtract two dates to find the number of days between those dates.
– Add hours to a date by dividing the number of hours by 24.

SELECT last_name, hire_date
FROM employees
WHERE last_name like ‘G%’;

SELECT last_name, (SYSDATE-hire_date)/7 AS WEEKS
FROM employees
WHERE department_id = 90;

Using Date Functions:

• MONTHS_BETWEEN (’01-SEP-95′,’11-JAN-94′) – 19.6774194
• ADD_MONTHS (’11-JAN-94′,6) – ’11-JUL-94′
• NEXT_DAY (’01-SEP-95′,’FRIDAY’)  – ’08-SEP-95′
• LAST_DAY(’01-FEB-95′) – ’28-FEB-95′

4. Conversion Functions:

Conversion Functions is also two types:

• Implicit data type conversion (VARCHAR2 or CHAR to NUMBER, VARCHAR2 or CHAR to DATE, NUMBER to VARCHAR2, DATE to VARCHAR2)
• Explicit data type conversion ( TO__CHAR(date,, ‘format__model’))

Elements of the Date Format Model:

YYYY – Full year in numbers
YEAR – Year spelled out
MM – Two-digit value for month
MON – Three-letter abbreviation of the month
MONTH -  Full name of the month
DY – Three-letter abbreviation of the day of the week
DAY – Full name of the day of the week
DD – Numeric day of the month

SELECT last_name,
TO_CHAR(hire_date, ‘fmDD Month YYYY’)
AS HIREDATE
FROM employees;

Nesting Functions:

• Single-row functions can be nested to any level.
• Nested functions are evaluated from deepest level to the least deep level.

Examples:

SELECT last_name,
NVL(TO_CHAR(manager_id), ‘No Manager’)
FROM employees
WHERE manager_id IS NULL;

5. General Functions:

These functions work with any data type and pertain to using nulls.
• NVL (expr1, expr2)
• NVL2 (expr1, expr2, expr3)
• NULLIF (expr1, expr2)
• COALESCE (expr1, expr2, …, exprn)

NVL Function:

Converts a null to an actual value.
• Data types that can be used are date, character, and number.
• Data types must match:
– NVL(commission_pct,0)
– NVL(hire_date,’01-JAN-97′)
– NVL(job_id,’No Job Yet’)

Example:

SELECT last_name, salary, NVL(commission_pct, 0),
(salary*12) + (salary*12*NVL(commission_pct, 0)) AN_SAL
FROM employees;

SELECT last_name, salary, commission_pct,
NVL2(commission_pct,
‘SAL+COMM’, ‘SAL’) income
FROM employees WHERE department_id IN (50, 80);

SELECT first_name, LENGTH(first_name) “expr1″,
last_name, LENGTH(last_name) “expr2″,
NULLIF(LENGTH(first_name), LENGTH(last_name)) result
FROM employees;