Oracle SQL Functions Chart

Introduction to Oracle SQL Functions

Welcome to our comprehensive guide on Oracle SQL functions are valuable resource for both novice and experienced database professionals. Whether you are just starting your journey with SQL or are an experienced developer looking to refine your skills, understanding Oracle SQL functions is essential for mastering data manipulation and analysis.

Oracle SQL functions are built-in tools that enable you to perform a wide range of operations on your data, from basic string manipulations to complex numerical calculations and date handling. They are designed to simplify and streamline queries, allowing you to achieve more with less effort. This guide will walk you through various categories of Oracle SQL functions, each equipped with detailed explanations and practical examples.

Why Use Oracle SQL Functions?

Efficiency: Functions can automate repetitive tasks, reducing the need for manual intervention. This not only speeds up query execution but also minimizes errors.
Versatility: Whether you need to transform data formats, aggregate results, or perform calculations, Oracle SQL functions offer the flexibility to handle diverse requirements.
Data Integrity: Built-in functions ensure that data is processed consistently and accurately, maintaining the integrity of your database operations.

What You'll Learn

In this guide, we delve into the following categories of Oracle SQL functions:

String Functions: These functions are used for manipulating text, allowing you to concatenate, extract, and format strings. For example, the CONCAT function joins two strings into one, while SUBSTR extracts a specific portion of a string.
Date Functions: Managing dates and times is crucial for time-based data analysis. Functions like SYSDATE, ADD_MONTHS, and EXTRACT provide powerful tools for date calculations and formatting.
Numeric Functions: For performing arithmetic operations and statistical analysis, Oracle SQL offers functions such as ROUND, TRUNC, and AVG. These functions help in rounding numbers, calculating averages, and more.
Conversion Functions: These functions convert data from one type to another, such as from string to date or number to string. Functions like TO_CHAR, TO_NUMBER, and TO_DATE are essential for data transformation tasks.
Aggregate Functions: Aggregate functions perform calculations on multiple rows of data and return a single value. Functions like SUM, COUNT, and MAX are commonly used to summarize data, calculate totals, and find extreme values.

Each section of this guide provides a clear overview of the function, its purpose, and practical examples to illustrate its use. By exploring these functions, you will gain a deeper understanding of how to leverage Oracle SQL's capabilities to enhance your data management and analysis tasks.

Getting Started

To make the most of this guide, start by familiarizing yourself with the function categories that are most relevant to your needs. Experiment with the provided SQL code examples in your Oracle database environment to see the functions in action. As you progress, you will discover how these functions can be combined to create powerful queries and reports.

We hope this guide serves as a valuable reference and enhances your proficiency with Oracle SQL functions. Dive in and start exploring the powerful tools at your disposal to take your SQL skills to the next level!

String Functions

Function	Description	Example
CONCAT	Concatenates two strings.	`SELECT CONCAT('Hello', ' World') FROM dual;` Result: Hello World
SUBSTR	Extracts a substring from a string.	`SELECT SUBSTR('Oracle SQL', 1, 6) FROM dual;` Result: Oracle
LENGTH	Returns the length of a string.	`SELECT LENGTH('Oracle SQL') FROM dual;` Result: 10
INSTR	Returns the position of a substring in a string.	`SELECT INSTR('Oracle SQL', 'SQL') FROM dual;` Result: 8
REPLACE	Replaces occurrences of a substring with another substring.	`SELECT REPLACE('Oracle SQL', 'SQL', 'PL/SQL') FROM dual;` Result: Oracle PL/SQL
TRIM	Removes leading and trailing spaces or specified characters from a string.	`SELECT TRIM(' SQL ') FROM dual;` Result: SQL
UPPER	Converts a string to uppercase.	`SELECT UPPER('oracle sql') FROM dual;` Result: ORACLE SQL
LOWER	Converts a string to lowercase.	`SELECT LOWER('ORACLE SQL') FROM dual;` Result: oracle sql
INITCAP	Capitalizes the first letter of each word in a string.	`SELECT INITCAP('oracle sql functions') FROM dual;` Result: Oracle Sql Functions
LPAD	Pads the left side of a string with a specified set of characters.	`SELECT LPAD('SQL', 10, '') FROM dual;` Result: *******SQL
RPAD	Pads the right side of a string with a specified set of characters.	`SELECT RPAD('SQL', 10, '') FROM dual;` Result: SQL*******
TRANSLATE	Replaces a sequence of characters in a string with another sequence of characters.	`SELECT TRANSLATE('Oracle', 'Oe', 'oE') FROM dual;` Result: oraclE
REGEXP_INSTR	Searches for a regular expression pattern in a string and returns the position.	`SELECT REGEXP_INSTR('Oracle SQL', 'SQL') FROM dual;` Result: 8
REGEXP_REPLACE	Replaces occurrences of a regular expression pattern with another string.	`SELECT REGEXP_REPLACE('Oracle SQL', '[^A-Za-z]', '') FROM dual;` Result: OracleSQL
REGEXP_SUBSTR	Extracts a substring that matches a regular expression pattern.	`SELECT REGEXP_SUBSTR('Oracle 123 SQL', '\d+', 1, 1) FROM dual;` Result: 123
CHR	Converts a number to a character.	`SELECT CHR(65) FROM dual;` Result: A
ASCII	Returns the ASCII value of the first character of a string.	`SELECT ASCII('A') FROM dual;` Result: 65
SOUNDEX	Returns a phonetic representation of a string.	`SELECT SOUNDEX('Smith') FROM dual;` Result: S530

Date Functions

Function	Description	Example
SYSDATE	Returns the current system date and time.	`SELECT SYSDATE FROM dual;` Result: Current system date and time
CURRENT_DATE	Returns the current date in the session time zone.	`SELECT CURRENT_DATE FROM dual;` Result: Current date in session time zone
CURRENT_TIMESTAMP	Returns the current date and time with time zone.	`SELECT CURRENT_TIMESTAMP FROM dual;` Result: Current date and time with time zone
SYSTIMESTAMP	Returns the system date and time with time zone and fractional seconds.	`SELECT SYSTIMESTAMP FROM dual;` Result: System date and time with time zone
ADD_MONTHS	Adds a number of months to a date.	`SELECT ADD_MONTHS(SYSDATE, 3) FROM dual;` Result: Date 3 months from now
MONTHS_BETWEEN	Returns the number of months between two dates.	`SELECT MONTHS_BETWEEN(SYSDATE, DATE '2023-01-01') FROM dual;` Result: Number of months between today and January 1, 2023
NEXT_DAY	Returns the date of the next specified weekday after a given date.	`SELECT NEXT_DAY(SYSDATE, 'FRIDAY') FROM dual;` Result: Date of the next Friday
LAST_DAY	Returns the last day of the month for a given date.	`SELECT LAST_DAY(SYSDATE) FROM dual;` Result: Last day of the current month
TRUNC	Truncates a date to a specified unit of measure.	`SELECT TRUNC(SYSDATE, 'YEAR') FROM dual;` Result: First day of the current year
ROUND	Rounds a date to a specified unit of measure.	`SELECT ROUND(SYSDATE, 'MONTH') FROM dual;` Result: First day of the current month rounded
EXTRACT	Extracts a part of a date, such as year, month, or day.	`SELECT EXTRACT(YEAR FROM SYSDATE) FROM dual;` Result: Current year
TO_DATE	Converts a string to a date.	`SELECT TO_DATE('2024-09-11', 'YYYY-MM-DD') FROM dual;` Result: September 11, 2024
TO_CHAR	Converts a date to a string.	`SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY') FROM dual;` Result: Current date in DD-MON-YYYY format
TO_TIMESTAMP	Converts a string to a timestamp.	`SELECT TO_TIMESTAMP('2024-09-11 12:30:45', 'YYYY-MM-DD HH24:MI:SS') FROM dual;` Result: Timestamp for September 11, 2024, 12:30:45
TO_YMINTERVAL	Converts a string to a YEAR TO MONTH interval.	`SELECT TO_YMINTERVAL('3-6') FROM dual;` Result: Interval of 3 years and 6 months
TO_DSINTERVAL	Converts a string to a DAY TO SECOND interval.	`SELECT TO_DSINTERVAL('2 03:15:30') FROM dual;` Result: Interval of 2 days, 3 hours, 15 minutes, 30 seconds

Numeric Functions

Function	Description	Example
ROUND	Rounds a number to a specified number of decimal places.	`SELECT ROUND(123.4567, 2) FROM dual;` Result: 123.46
TRUNC	Truncates a number to a specified number of decimal places.	`SELECT TRUNC(123.4567, 2) FROM dual;` Result: 123.45
CEIL	Returns the smallest integer greater than or equal to a number.	`SELECT CEIL(123.45) FROM dual;` Result: 124
FLOOR	Returns the largest integer less than or equal to a number.	`SELECT FLOOR(123.45) FROM dual;` Result: 123
MOD	Returns the remainder of a number divided by another number.	`SELECT MOD(10, 3) FROM dual;` Result: 1
POWER	Returns the result of a number raised to the power of another number.	`SELECT POWER(2, 3) FROM dual;` Result: 8
SQRT	Returns the square root of a number.	`SELECT SQRT(16) FROM dual;` Result: 4
EXP	Returns e raised to the power of a number.	`SELECT EXP(1) FROM dual;` Result: 2.71828
LOG	Returns the natural logarithm of a number.	`SELECT LOG(10) FROM dual;` Result: 2.30259
RADIANS	Converts degrees to radians.	`SELECT RADIANS(180) FROM dual;` Result: 3.14159
DEGREES	Converts radians to degrees.	`SELECT DEGREES(PI()) FROM dual;` Result: 180
SIGN	Returns the sign of a number.	`SELECT SIGN(-10) FROM dual;` Result: -1
ABS	Returns the absolute value of a number.	`SELECT ABS(-123.45) FROM dual;` Result: 123.45

Conversion Functions

Function	Description	Example
TO_CHAR	Converts a number or date to a string.	`SELECT TO_CHAR(1234.56, '9999.99') FROM dual;` Result: 1234.56
TO_NUMBER	Converts a string to a number.	`SELECT TO_NUMBER('1234.56') FROM dual;` Result: 1234.56
TO_DATE	Converts a string to a date.	`SELECT TO_DATE('2024-09-11', 'YYYY-MM-DD') FROM dual;` Result: September 11, 2024
TO_TIMESTAMP	Converts a string to a timestamp.	`SELECT TO_TIMESTAMP('2024-09-11 12:30:45', 'YYYY-MM-DD HH24:MI:SS') FROM dual;` Result: September 11, 2024, 12:30:45
CAST	Converts one data type to another.	`SELECT CAST('1234.56' AS NUMBER) FROM dual;` Result: 1234.56

Aggregate Functions

Function	Description	Example
SUM	Calculates the sum of a numeric column.	`SELECT SUM(salary) FROM employees;` Result: Total salary of all employees
AVG	Calculates the average of a numeric column.	`SELECT AVG(salary) FROM employees;` Result: Average salary of all employees
COUNT	Counts the number of rows in a table.	`SELECT COUNT(*) FROM employees;` Result: Total number of employees
MAX	Finds the maximum value of a numeric column.	`SELECT MAX(salary) FROM employees;` Result: Highest salary
MIN	Finds the minimum value of a numeric column.	`SELECT MIN(salary) FROM employees;` Result: Lowest salary
GROUP_CONCAT	Concatenates values from multiple rows into a single string (available in MySQL, use LISTAGG in Oracle).	`SELECT LISTAGG(name, ', ') WITHIN GROUP (ORDER BY name) FROM employees;` Result: Concatenated employee names
VARIANCE	Calculates the variance of a numeric column.	`SELECT VARIANCE(salary) FROM employees;` Result: Variance in salaries
STDDEV	Calculates the standard deviation of a numeric column.	`SELECT STDDEV(salary) FROM employees;` Result: Standard deviation of salaries