Oracle useful functions - string manipulation, number calculations, date conversion and aggregation

Oracle provides many built-in functions that are useful for string manipulation, number calculations, date operations, and data aggregation. Here are some of the most commonly used Oracle functions:

---

🔹 String Functions

Function	Description	Example
UPPER()	Converts to uppercase	UPPER('oracle') → 'ORACLE'
LOWER()	Converts to lowercase	LOWER('ORACLE') → 'oracle'
INITCAP()	Capitalizes first letter of each word	INITCAP('hello world') → 'Hello World'
LENGTH()	Returns length of string	LENGTH('Oracle') → 6
SUBSTR()	Extracts substring	SUBSTR('Oracle', 2, 3) → 'rac'
INSTR()	Finds position of substring	INSTR('Oracle', 'a') → 3
REPLACE()	Replaces substring	REPLACE('hello world', 'world', 'Oracle') → 'hello Oracle'
REGEXP_SUBSTR()	Extracts substring using regex	REGEXP_SUBSTR('123-456-789', '\d+', 1, 2) → '456'
LISTAGG()	Aggregates multiple rows into a single string	LISTAGG(name, ', ') WITHIN GROUP (ORDER BY name)

---

🔹 Number Functions

Function	Description	Example
ROUND()	Rounds number to given decimal places	ROUND(123.456, 2) → 123.46
TRUNC()	Truncates number without rounding	TRUNC(123.456, 2) → 123.45
MOD()	Returns remainder	MOD(10, 3) → 1
CEIL()	Rounds up to nearest integer	CEIL(4.2) → 5
FLOOR()	Rounds down to nearest integer	FLOOR(4.9) → 4
ABS()	Returns absolute value	ABS(-10) → 10
SIGN()	Returns sign (-1, 0, 1)	SIGN(-50) → -1
POWER()	Raises number to a power	POWER(2, 3) → 8

---

🔹 Date Functions

Function	Description	Example
SYSDATE	Returns current date and time	SELECT SYSDATE FROM DUAL;
SYSTIMESTAMP	Returns current timestamp with timezone	SELECT SYSTIMESTAMP FROM DUAL;
ADD_MONTHS()	Adds months to a date	ADD_MONTHS(SYSDATE, 3)
MONTHS_BETWEEN()	Finds difference between two dates in months	MONTHS_BETWEEN('01-JAN-2024', '01-JUL-2024') → 6
LAST_DAY()	Returns last day of month	LAST_DAY(SYSDATE)
NEXT_DAY()	Returns next given weekday	NEXT_DAY(SYSDATE, 'Monday')
TRUNC()	Truncates date to specific unit	TRUNC(SYSDATE, 'MONTH')
EXTRACT()	Extracts part of date	EXTRACT(YEAR FROM SYSDATE) → 2024

---

🔹 Conversion Functions

Function	Description	Example
TO_CHAR()	Converts date/number to string	TO_CHAR(SYSDATE, 'DD-MON-YYYY')
TO_DATE()	Converts string to date	TO_DATE('2024-01-01', 'YYYY-MM-DD')
TO_NUMBER()	Converts string to number	TO_NUMBER('12345')

---

🔹 Aggregate Functions

Function	Description	Example
SUM()	Returns sum of values	SELECT SUM(salary) FROM employees;
AVG()	Returns average	SELECT AVG(salary) FROM employees;
MIN()	Returns minimum value	SELECT MIN(salary) FROM employees;
MAX()	Returns maximum value	SELECT MAX(salary) FROM employees;
COUNT()	Returns count of rows	SELECT COUNT(*) FROM employees;

---

🔹 Analytical Functions

Function	Description	Example
RANK()	Assigns rank with gaps	RANK() OVER (ORDER BY salary DESC)
DENSE_RANK()	Assigns rank without gaps	DENSE_RANK() OVER (ORDER BY salary DESC)
ROW_NUMBER()	Assigns unique row number	ROW_NUMBER() OVER (ORDER BY salary DESC)
LEAD()	Gets next row's value	LEAD(salary) OVER (ORDER BY salary)
LAG()	Gets previous row's value	LAG(salary) OVER (ORDER BY salary)

---

🔹 Miscellaneous Functions

Function	Description	Example
NVL()	Replaces NULL with a value	NVL(NULL, 'Default') → 'Default'
NVL2()	Returns different values for NULL/non-NULL	NVL2(salary, 'Present', 'Missing')
DECODE()	IF-THEN-ELSE alternative	DECODE(status, 'A', 'Active', 'I', 'Inactive', 'Unknown')
CASE	More flexible conditional logic	CASE WHEN status = 'A' THEN 'Active' ELSE 'Inactive' END

---

🔹 Regular Expression Functions (Regex)

Function	Description	Example
REGEXP_LIKE()	Checks if value matches pattern	REGEXP_LIKE('123ABC', '^[0-9]+$') → FALSE
REGEXP_INSTR()	Finds position of regex pattern	REGEXP_INSTR('abc123xyz', '\d+') → 4
REGEXP_REPLACE()	Replaces regex match	REGEXP_REPLACE('a1b2c3', '\d', '') → 'abc'
REGEXP_SUBSTR()	Extracts matching substring	REGEXP_SUBSTR('a1b2c3', '\d+') → '1'

---

✨ Summary

🔹 String Functions → UPPER, SUBSTR, LISTAGG, REGEXP_REPLACE
🔹 Number Functions → ROUND, MOD, POWER
🔹 Date Functions → SYSDATE, LAST_DAY, EXTRACT
🔹 Conversion Functions → TO_CHAR, TO_DATE, TO_NUMBER
🔹 Aggregate Functions → SUM, AVG, COUNT
🔹 Analytical Functions → RANK, ROW_NUMBER, LEAD
🔹 Miscellaneous Functions → NVL, DECODE, CASE