List of Build in Functions - Oracle

 

 Oracle provides a comprehensive set of built-in functions categorized into various types. Here’s a list of the commonly used categories and functions:

1. String Functions

• LOWER(char) – Converts to lowercase
• UPPER(char) – Converts to uppercase
• INITCAP(char) – Capitalizes the first letter
• LENGTH(char) – Returns length of a string
• SUBSTR(char, start, length) – Extracts substring
• INSTR(char, substring) – Finds position of substring
• TRIM(char) – Removes leading/trailing characters
• LPAD(char, length, pad_char) – Left pad
• RPAD(char, length, pad_char) – Right pad
• REPLACE(char, search, replace) – Replaces text
• CONCAT(char1, char2) – Concatenates strings

---

2. Numeric Functions

• ABS(number) – Absolute value
• ROUND(number, decimal_places) – Rounds to n decimal places
• TRUNC(number, decimal_places) – Truncates to n decimal places
• MOD(number1, number2) – Remainder of division
• CEIL(number) – Rounds up to next whole number
• FLOOR(number) – Rounds down to previous whole number
• POWER(base, exponent) – Raises to the power
• SQRT(number) – Square root
• SIGN(number) – Returns -1, 0, or 1

---

3. Date Functions

• SYSDATE – Returns current date and time
• CURRENT_DATE – Date in current session timezone
• ADD_MONTHS(date, number) – Adds months to date
• LAST_DAY(date) – Last day of the month
• MONTHS_BETWEEN(date1, date2) – Difference in months
• NEXT_DAY(date, weekday) – Next specified weekday
• ROUND(date, format) – Rounds date to the nearest unit
• TRUNC(date, format) – Truncates date to specified unit

---

4. Conversion Functions

• TO_CHAR(date_or_number, format) – Converts to string
• TO_DATE(char, format) – Converts to date
• TO_NUMBER(char) – Converts to number

---

5. Aggregate Functions

• AVG(column) – Average
• COUNT(column) – Number of rows
• SUM(column) – Sum
• MAX(column) – Maximum
• MIN(column) – Minimum
• STDDEV(column) – Standard deviation
• VARIANCE(column) – Variance

---

6. Conditional Functions

• CASE WHEN condition THEN result ELSE result END – Conditional logic
• DECODE(expr, search, result, default) – Compares values
• NVL(expr1, expr2) – Replace NULL
• NVL2(expr1, expr2, expr3) – Replace based on NULL check
• NULLIF(expr1, expr2) – Returns NULL if equal

---

7. Miscellaneous Functions

• USER – Returns the current user
• UID – Returns user ID
• SYS_CONTEXT(namespace, parameter) – Returns environment context
• ROWNUM – Row number in result set
• ROWID – Unique row identifier

This list covers most of the built-in functions you will encounter in Oracle SQL. Each function plays a specific role in manipulating or retrieving data effectively.