Oracle Database offers a rich set of built-in functions to perform various operations on data. These functions can be categorized into several types:
1. Arithmetic Functions
Arithmetic functions are used to perform mathematical calculations on numeric data.
Examples:
ABS(number): Returns the absolute value of a number.
CEIL(number): Returns the smallest integer greater than or equal to a number.
FLOOR(number): Returns the largest integer less than or equal to a number.
MOD(number1, number2): Returns the remainder of number1 divided by number2.
ROUND(number, precision): Rounds a number to the specified precision.
TRUNC(number, precision): Truncates a number to the specified precision.
Example:
SELECT ABS(-10), CEIL(3.14), FLOOR(3.14), MOD(10, 3), ROUND(3.14159, 2), TRUNC(3.14159, 2) FROM DUAL;
2. Character Functions
Character functions are used to manipulate character strings.
Examples:
CONCAT(str1, str2): Concatenates two strings.
LENGTH(str): Returns the length of a string.
SUBSTR(str, start_position, length): Extracts a substring from a string.
UPPER(str): Converts a string to uppercase.
LOWER(str): Converts a string to lowercase.
INITCAP(str): Capitalizes the first letter of each word in a string.
Example:
SELECT CONCAT('Hello', ' World'), LENGTH('Oracle'), SUBSTR('Oracle Database', 4, 7), UPPER('oracle'), LOWER('ORACLE'), INITCAP('oracle database') FROM DUAL;
3. Date Functions
Date functions are used to manipulate date and time values.
Examples:
SYSDATE: Returns the current system date and time.
ADD_MONTHS(date, number): Adds a specified number of months to a date.
LAST_DAY(date): Returns the last day of the month for a given date.
MONTHS_BETWEEN(date1, date2): Calculates the number of months between two dates.
Example:
SELECT SYSDATE, ADD_MONTHS(SYSDATE, 3), LAST_DAY(SYSDATE), MONTHS_BETWEEN(TO_DATE('2023-01-01'), TO_DATE('2023-03-31')) FROM DUAL;
4. Group Functions
Group functions are used to perform calculations on groups of rows.
Examples:
COUNT(*): Counts the number of rows.
SUM(column_name): Calculates the sum of values in a column.
AVG(column_name): Calculates the average of values in a column.
MIN(column_name): Returns the minimum value in a column.
MAX(column_name): Returns the maximum value in a column.
Example:
SELECT COUNT(*), SUM(salary), AVG(salary), MIN(salary), MAX(salary) FROM employees;
By mastering these functions, you can write complex and efficient SQL queries to extract valuable insights from your data.
Comments