Top 100 Oracle SQL Function You Must Know
Before diving into the top 100 Oracle SQL function, it’s helpful to understand the major categories these functions fall into. Each group serves a specific purpose in data handling, transformation, or querying. Here’s a brief overview:
Let’s break each one down with simple examples and real-world usage.
🔹1. Oracle SQL Function for Handling NULL Values
In Oracle SQL, NULL represents the absence of a value—meaning the data is unknown, missing, or not applicable. Unlike a zero or an empty string, NULL is a special marker used to indicate that no value has been assigned. Because of this, working with NULL values requires special care; simple comparisons (like = NULL) won’t work as expected.
Oracle provides a set of NULL-handling functions that help manage and transform these values for cleaner reporting and accurate logic in SQL queries.
| # | Function | Purpose |
|---|---|---|
| 1 | NVL | Replace NULL with a default value |
| 2 | NVL2 | Return one value if NOT NULL, another if NULL |
| 3 | COALESCE | Return first non-NULL expression |
| 4 | NULLIF | Return NULL if two expressions are equal |
| 5 | LNNVL | Conditional evaluation of NULLs |
🔹2. Oracle SQL Function for String
String functions allow you to manipulate text data—such as trimming, concatenating, replacing, or extracting substrings. These are particularly useful in data formatting, cleansing, and transformations in reports or application outputs.
| # | Function | Purpose |
|---|---|---|
| 6 | SUBSTR | Extract substring |
| 7 | INSTR | Find position of substring |
| 8 | LENGTH | Length of string |
| 9 | TRIM | Trim leading/trailing characters |
| 10 | LTRIM | Trim leading characters |
| 11 | RTRIM | Trim trailing characters |
| 12 | REPLACE | Replace characters in string |
| 13 | TRANSLATE | Replace multiple characters at once |
| 14 | LOWER | Convert to lowercase |
| 15 | UPPER | Convert to uppercase |
| 16 | INITCAP | Capitalize first letter of each word |
| 17 | CONCAT | Concatenate two strings |
| 18 | LPAD | Left pad a string |
| 19 | RPAD | Right pad a string |
| 20 | CHR | Return character from ASCII code |
| 21 | ASCII | Return ASCII code from character |
| 22 | REGEXP_SUBSTR | Regex-based substring search |
| 23 | REGEXP_REPLACE | Regex-based search and replace |
| 24 | REGEXP_INSTR | Regex-based position search |
| 25 | REGEXP_LIKE | Regex pattern matching |
🔹 3. Oracle SQL Function for Number
Numeric functions handle operations involving numbers. These include mathematical calculations, rounding, truncating, or generating random values. They’re essential for financial, statistical, or scientific computations in SQL queries.
| # | Function | Purpose |
|---|---|---|
| 26 | ROUND | Round number to decimals |
| 27 | TRUNC | Truncate decimals |
| 28 | MOD | Get remainder |
| 29 | ABS | Absolute value |
| 30 | CEIL | Round up |
| 31 | FLOOR | Round down |
| 32 | SIGN | Returns sign of number |
| 33 | POWER | Exponentiation |
| 34 | SQRT | Square root |
| 35 | EXP | Exponential value |
| 36 | LOG | Natural log |
| 37 | LN | Log base e |
| 38 | GREATEST | Max of multiple expressions |
| 39 | LEAST | Min of multiple expressions |
🔹 4. Oracle SQL Function for Date & Time
Date functions help you process and format date and time values. You can add or subtract days, extract specific parts (like year or month), or compare and format date values in user-friendly ways.
| # | Function | Purpose |
|---|---|---|
| 40 | SYSDATE | Current date/time |
| 41 | CURRENT_DATE | Current user session date |
| 42 | SYSTIMESTAMP | Current timestamp |
| 43 | LOCALTIMESTAMP | Timestamp without time zone |
| 44 | ADD_MONTHS | Add/subtract months |
| 45 | MONTHS_BETWEEN | Months between dates |
| 46 | NEXT_DAY | Next specified weekday |
| 47 | LAST_DAY | Last day of the month |
| 48 | TRUNC(date) | Truncate date |
| 49 | ROUND(date) | Round date to nearest unit |
| 50 | EXTRACT | Extract date parts (year, month, etc.) |
🔹 5. Oracle SQL Function for Conversion
Conversion functions convert one data type into another—like converting strings to numbers, dates to text, or binary to hex. These are crucial when dealing with mismatched data formats or when preparing data for display.
| # | Function | Purpose |
|---|---|---|
| 51 | TO_CHAR | Convert to string |
| 52 | TO_DATE | Convert to date |
| 53 | TO_NUMBER | Convert to number |
| 54 | CAST | General type conversion |
| 55 | CONVERT | Character set conversion |
| 56 | DUMP | Internal representation of value |
| 57 | BIN_TO_NUM | Binary to number |
| 58 | NUMTODSINTERVAL | Number to INTERVAL DAY TO SECOND |
| 59 | NUMTOYMINTERVAL | Number to INTERVAL YEAR TO MONTH |
🔹 6. Conditional Logic
Conditional functions evaluate conditions and return values based on logic. Functions like NVL, COALESCE, and DECODE simplify decision-making within SQL queries, often replacing complex CASE statements.
| # | Function | Purpose |
|---|---|---|
| 60 | DECODE | Conditional match |
| 61 | CASE | Complex condition handling |
| 62 | NVL2 | Return value based on NULL check (again) |
| 63 | NULLIF | Return NULL if values match (again) |
🔹 7. Aggregate Functions
Aggregate functions perform calculations on sets of rows and return a single value. They are frequently used in GROUP BY queries to summarize data. Examples include SUM, AVG, COUNT, MIN, and MAX. These are core components in analytical reports and dashboards.
| # | Function | Purpose |
|---|---|---|
| 64 | SUM | Total of column values |
| 65 | AVG | Average |
| 66 | MAX | Maximum |
| 67 | MIN | Minimum |
| 68 | COUNT | Count rows |
| 69 | STDDEV | Standard deviation |
| 70 | VARIANCE | Statistical variance |
| 71 | GROUPING | Check if column is aggregated |
| 72 | CUBE | Multidimensional aggregations |
| 73 | ROLLUP | Hierarchical aggregations |
🔹 8. Analytic (Window) Functions
Analytical functions operate on a set of rows related to the current row and are used in advanced reporting. They don’t reduce the number of rows like aggregate functions. Examples include RANK, DENSE_RANK, ROW_NUMBER, and LEAD/LAG. These are vital for cumulative totals, moving averages, and comparative reports.
| # | Function | Purpose |
|---|---|---|
| 74 | RANK() | Rank of row |
| 75 | DENSE_RANK() | Dense rank |
| 76 | ROW_NUMBER() | Row number |
| 77 | NTILE(n) | Divide rows into buckets |
| 78 | LAG() | Value of previous row |
| 79 | LEAD() | Value of next row |
| 80 | FIRST_VALUE() | First value in window |
| 81 | LAST_VALUE() | Last value in window |
| 82 | NTH_VALUE() | N-th value in window |
| 83 | PERCENT_RANK() | Percentile rank |
| 84 | CUME_DIST() | Cumulative distribution |
🔹 9. Miscellaneous Functions
These functions return information about the session or the system, such as USER, SYSDATE, UID, and USERENV. They help identify runtime conditions, such as the logged-in user or current system date, which is useful for auditing and personalization.
| # | Function | Purpose |
|---|---|---|
| 85 | UID | Return user ID |
| 86 | USER | Return current user |
| 87 | USERENV | Session info (language, schema) |
| 88 | VSIZE | Size in bytes |
| 89 | SYS_CONTEXT | Secure app context values |
| 90 | ORA_HASH | Hash function |
| 91 | SYS_GUID() | Generate unique identifier |
| 92 | EMPTY_BLOB() | Initialize empty LOB |
| 93 | EMPTY_CLOB() | Initialize empty LOB |
🔹 10. Advanced / Newer Functions
| # | Function | Purpose |
|---|---|---|
| 94 | LISTAGG | Aggregate rows into string |
| 95 | JSON_OBJECT | Create JSON object |
| 96 | JSON_VALUE | Extract value from JSON |
| 97 | JSON_TABLE | Query JSON as rows |
| 98 | XMLAGG | Aggregate XML fragments |
| 99 | PIVOT | Convert rows to columns |
| 100 | UNPIVOT | Convert columns to rows |
General Overview of Oracle SQL Functions
1. SQL Function NVL
Replaces a NULL value with a specified default. It ensures that queries do not return NULL when a default is more meaningful, especially in aggregations or reports.
Use Cases of SQL Function NVL
Financial reporting (show 0 when commission is NULL), avoiding NULL in calculations or joins.
Syntax of SQL Function NVL
NVL(expr1, expr2)
- expr1: The value to evaluate.
- expr2: The substitute if expr1 is NULL.
Example of SQL Function NVL
SELECT employee_name, NVL(commission, 0) AS actual_commission
FROM employees;Shows 0 in the commission column where it is NULL.
2. SQL Function NVL2
Returns one value if the first expression is NOT NULL and another if it IS NULL. It’s a more flexible version of NVL.
Use Cases of SQL Function NVL2
Status flags like ‘Has Commission’ vs ‘No Commission’; formatting output based on data presence.
NVL2 Syntax:
NVL2(expr1, value_if_not_null, value_if_null)NVL2 Example:
SELECT employee_name, NVL2(commission, 'Has Commission',
'No Commission') AS status
FROM employees;Outputs status based on whether commission is present.
3. SQL Function COALESCE
Returns the first non-NULL expression from a list. It evaluates left to right and returns the first non-null value it encounters.
Use Cases of SQL Function COALESCE
Use when checking multiple fallback columns for a valid value—e.g., primary email, then phone number, then eft_swift_code.
Syntax of SQL Function COALESCE:
COALESCE(expr1, expr2, ..., expr_n)Example of SQL Function COALESCE:
SELECT contact_point_id, contact_point_type contact_type, COALESCE(email_address, phone_number, eft_swift_code) AS contact_info
FROM hz_contact_points;Returns the first available contact info.
4. SQL Function NULLIF
Compares two expressions; if they are equal, returns NULL; otherwise returns the first expression. Useful in avoiding divide-by-zero or flagging identical values.
Use Cases of SQL Function NULLIF
Avoid divide-by-zero by using NULLIF in denominator; suppress duplicate data in analytics.
Syntax of SQL Function NULLIF:
NULLIF(expr1, expr2)Example of SQL Function NULLIF:
SELECT NULLIF(100, 100) FROM dual;Returns NULL because the values are equal.
5. SQL Function LNNVL (Logical Negation for Null-Value Logic)
LNNVL (Logical Negation of NULL-Value Logic) is used to evaluate conditions that may involve NULL values. Unlike regular logical conditions, LNNVL helps to handle unknown (NULL) outcomes in WHERE clauses, returning a Boolean result when standard logic cannot.
It’s particularly useful when you want to apply filtering logic that gracefully handles NULL values without failing or skipping evaluations.
Use Cases of LNNVL
- Filtering rows where a column may be NULL and you want to include them in logic-based queries.
- Handling complex WHERE clause conditions where NULL should be treated as FALSE.
- Alternative to NVL(condition, FALSE) for pure logical conditions.
Syntax of SQL FunctionLNNVL
LNNVL(condition)Condition: A logical expression that may return TRUE, FALSE, or UNKNOWN (NULL).
Example of SQL Function LNNVL
🧪 Example 1: Basic Use
Return employees whose salary is not greater than 10000 or salary is NULL:
SELECT employee_name, salary
FROM employees
WHERE LNNVL(salary > 10000);This will return:
- Employees with salary less than or equal to 10000.
- Employees with NULL salary (which would be skipped in a regular salary > 10000 condition).
🧪 Example 2: Safer NULL Handling in Conditions
SELECT *
FROM orders
WHERE LNNVL(discount > 0.05);This query returns:
- Orders with discount <= 0.05
- Orders where discount is NULL
LNNVL is only allowed in the WHERE clause.
It is useful for improving the completeness of logical expressions without having to write complex CASE or NVL logic.
6. SQL Function SUBSTR
Extracts a substring from a given string, starting from a position and for a specified length.
Use Cases of SQL Function SUBSTR
Extracting product codes, initials, date parts from strings.
Syntax of SQL Function SUBSTR
SUBSTR(string, start_position [, length])
Example of SQL Function SUBSTR
SELECT SUBSTR('PROD_1234_XYZ', 6, 4) AS item_code
FROM dual;Returns ‘1234’, the substring starting at position 6 and length of 4 characters.
7. SQL Function INSTR
Returns the position of a substring within a string. It’s useful for locating patterns or delimiters in text.
Use Cases of SQL Function INSTR
Extracting domain from email; parsing CSV or delimited strings.
Syntax:
Syntax of SQL Function INSTR
INSTR(string, substring [, start_position [, occurrence]])
Example of SQL Function INSTR
SELECT INSTR('Oracle SQL Guide', 'SQL') AS position
FROM dual;Returns 8, the position where ‘SQL’ starts.
8. SQL Function LENGTH
Returns the number of characters in a string. Useful for validation, truncation logic, or conditional formatting.
Use Cases of SQL Function LENGTH
Checking length before storing user input; validating codes.
Syntax of SQL Function LENGTH
LENGTH(string)
Example of SQL Function LENGTH:
SELECT LENGTH('Oracle') AS str_length
FROM dual;Returns 6, the length of string “Oracle”
9. SQL Function TRIM
Removes specified characters (or whitespace by default) from the beginning and/or end of a string.
Use Cases of SQL Function TRIM
Cleaning up user input, ensuring consistent formatting before comparison.
Syntax of SQL Function TRIM
TRIM([LEADING | TRAILING | BOTH] trim_character FROM string)
Example of SQL Function TRIM
SELECT TRIM(' ' FROM ' Oracle ') AS cleaned
FROM dual;Returns ‘Oracle’.
10. SQL Function LTRIM
Removes leading characters (defaults to space) from a string.
Use Cases of SQL Function LTRIM
Left-aligned formatting; parsing file names or strings with leading symbols.
Syntax of SQL Function LTRIM
LTRIM(string [, trim_characters])Example of SQL Function LTRIM
SELECT LTRIM('***Oracle', '*') AS result FROM dual;Returns ‘Oracle’.
11. SQL Function RTRIM
Removes trailing characters (defaults to space) from a string.
Use Case of SQL Function RTRIM
Similar to LTRIM but for right-end cleanup. Useful in legacy file parsing.
Syntax of SQL Function RTRIM
RTRIM(string [, trim_characters])Example of SQL Function RTRIM
SELECT RTRIM('Oracle***', '*') AS result FROM dual;Returns ‘Oracle’.
12. SQL Function REPLACE
Substitutes occurrences of a substring with another string. Helps in data cleansing and formatting.
Use Cases of SQL Function REPLACE
Removing special characters, converting codes, or adjusting formats.
Syntax of SQL Function REPLACE
REPLACE(string, search_string, replacement_string)
Example of SQL Function REPLACE
SELECT REPLACE('2024-06-30', '-', '/') AS formatted_date
FROM dua13. SQL Function TRANSLATE
TRANSLATE is a character-level substitution function in Oracle SQL. It replaces each character in a source string with a corresponding character from a replacement string, character by character and positionally mapped. Unlike REPLACE, which works on substrings, TRANSLATE works at the single-character level, making it highly efficient for tasks like character filtering, encoding, or sanitization.
🎯 Use Cases of SQL Function TRANSLATE
- ✅ Data cleansing: Remove or substitute unwanted characters (e.g., symbols, punctuation).
- ✅ Character masking: Replace specific characters for privacy or formatting.
- ✅ Encoding: Convert characters into a mapped representation (e.g., internal encoding).
- ✅ Replacing multiple characters in one go, unlike using multiple nested REPLACE() calls.
- ✅ Removing characters: Map to an empty position by omitting characters in the replacement string.
📚 Syntax of SQL Function TRANSLATE
TRANSLATE(source_string, from_string, to_string)- source_string: The string to modify.
- from_string: Characters to find in source_string.
- to_string: Characters to substitute for each character found in from_string. If shorter, characters are removed.
🧪 Example 1: SQL Function Replace characters
Replace 1, 2, and 3 with A, B and C respectively:
SELECT TRANSLATE('Order123', '123', 'ABC') AS translated_value
FROM dual;Output: OrderABC
🧪 Example 2: SQL FunctionRemove unwanted characters
Remove hyphens, slashes, and periods from a string:
SELECT TRANSLATE('2024-06/19.', '-/.', '') AS cleaned_value
FROM dual;
-- Output: 20240619Note: Characters in from_string with no matching position in to_string are removed.
🧪 Example 3: Simplified character encoding
Convert vowels to numbers:
SELECT TRANSLATE('DATABASE', 'AEIOU', '12345') AS encoded
FROM dual;
-- Output: D1T1B1S2✅ Notes:
- If from_string is longer than to_string, characters without a replacement are deleted.
- It is case-sensitive.
- Works best for character substitution—not suitable for pattern-based replacements (use REGEXP_REPLACE for that).
14. SQL Function LOWER
Converts all characters in a string to lowercase.
Use Cases of SQL Function LOWER
Normalizing data for comparisons, filtering, or storage.
Syntax of SQL Function LOWER
LOWER(string)Example of SQL Function LOWER
SELECT LOWER('Oracle SQL') AS lower_text FROM dual;Returns ‘oracle sql’.
15. SQL Function UPPER
Converts all characters in a string to uppercase. Useful for standardizing text data before comparison.
Use Cases of SQL Function UPPER
Ensuring case-insensitive searches; formatting user inputs or reference data.
Syntax of SQL Function UPPER
UPPER(stringExample of SQL Function UPPER
SELECT UPPER('oracle sql') AS upper_text FROM dual;Returns ‘ORACLE SQL’.
16. SQL Function INITCAP
Capitalizes the first letter of each word in a string and converts the rest to lowercase.
Use Cases of SQL Function INITCAP
Formatting names, titles, or addresses in reports.
Syntax of SQL Function INITCAP
INITCAP(string)REGEXPExample of SQL Function INITCAP
SELECT INITCAP('welcome to oracle sql') AS title_case FROM dual;Returns ‘Welcome To Oracle Sql’.
17. SQL Function CONCAT
Joins two strings together into one. Equivalent to using ||, but limited to two strings.
Use Cases of CONCAT
Concatenating names, codes, or building messages.
Syntax of SQL Function CONCAT
CONCAT(string1, string2)Example of SQL Function CONCAT
SELECT CONCAT('Oracle', ' SQL') AS result FROM dual;Returns ‘Oracle SQL’.
🔹 18. SQL Function LPAD
LPAD (Left Pad) adds characters to the left side of a string to make it a specific length. If the string is shorter than the specified length, padding characters are added; if longer, the string is truncated.
🎯 Use Cases of SQL Function LPAD:
- ✅ Formatting report columns to align text or numbers.
- ✅ Creating fixed-width file exports.
- ✅ Prefixing numeric codes with leading zeros or special characters.
📚 Syntax of SQL Function LPAD:
LPAD(source_string, total_length, pad_char)- source_string: The original string to be padded.
- total_length: Desired length of the output string.
- pad_char: The character used for padding (default is space if omitted).
🧪 Example: Left-pad with zeros
SELECT LPAD('123', 6, '0') AS padded_code
FROM dual;
-- Output: 000123🧪 Example: Align text in a 10-character column
SELECT LPAD('Cat', 10, '.') AS padded_text
FROM dual;
-- Output: .......Cat🔹 19. SQL Function RPAD
RPAD (Right Pad) works like LPAD, but it adds characters to the right of the original string to match the desired length.
🎯 Use Cases of SQL Function RPAD:
- ✅ Aligning text in reports and UIs.
- ✅ Creating text-based barcodes or pseudo-formatting.
- ✅ Constructing fixed-length record layouts.
📚 Syntax of SQL Function RPAD:
RPAD(source_string, total_length, pad_char)
🧪 Example: Right-pad with spaces
SELECT RPAD('Sam', 8) AS result
FROM dual;
-- Output: 'Sam '🧪 Example: Pad with hyphens
SELECT RPAD('A1', 5, '-') AS padded
FROM dual;
-- Output: A1---🔹20. SQL Function CHR
CHR returns the character associated with a given ASCII (or Unicode) numeric code. It’s useful for adding non-printing characters like line feeds, tabs, or specific symbols in queries.
🎯 Use Cases of SQL Function CHR
- ✅ Insert control characters (like newline CHR(10) or tab CHR(9)).
- ✅ Dynamically generate special character content.
- ✅ ASCII-based character manipulations or encoding.
📚 Syntax of SQL Function CHR
CHR(n)
- n: The ASCII or Unicode code to convert.
🧪 Example: Insert line break
SELECT 'Line1' || CHR(10) || 'Line2' AS result
FROM dual;🧪 Example: Get character for ASCII code 65
SELECT CHR(65) AS letter
FROM dual;
-- Output: A🔹 21. SQL Function ASCII
ASCII does the opposite of CHR—it returns the numeric ASCII code for the first character in a string.
🎯 Use Cases of SQL Function ASCII
- ✅ Determine character encoding.
- ✅ Compare characters numerically.
- ✅ Validate or filter special characters.
📚 Syntax of SQL Function ASCII
ASCII(char)char: A single character or string; only the first character is evaluated.
🧪 Example: Get ASCII value of a character
SELECT ASCII('A') AS code
FROM dual;
-- Output: 65🧪 Example: Check ASCII of space character
SELECT ASCII(' ') AS code
FROM dual;
-- Output: 3222. SQL Function REGEXP_SUBSTR
Returns the substring that matches a regular expression pattern.
Use Cases of SQL Function REGEXP_SUBSTR
Extracting specific patterns like IDs, codes, or keywords from text.
Syntax of SQL Function REGEXP_SUBSTR
REGEXP_SUBSTR(source_string, pattern [, position [, occurrence [, match_param]]])
Example of SQL Function REGEXP_SUBSTR
SELECT REGEXP_SUBSTR('ID-9876543-XYZ', '[0-9]+') AS extracted_id
FROM dual;Returns ‘9876543’.
23. SQL Function REGEXP_REPLACE
Replaces all or part of a string that matches a regular expression pattern.
Use Cases of SQL Function REGEXP_REPLACE
Data cleansing (removing special characters, correcting formats).
Syntax of SQL Function REGEXP_REPLACE
REGEXP_REPLACE(source_string, pattern, replacement [, position [, occurrence [, match_param]]])Example of SQL Function REGEXP_REPLACE
SELECT REGEXP_REPLACE('abc123xyz', '[0-9]', '') AS cleaned
FROM dual;Returns ‘abcxyz’ (removes digits).
24. SQL Function REGEXP_INSTR
Returns the position of a pattern match using regular expressions.
Use Cases of SQL Function REGEXP_INSTR
Finding position of substrings with complex rules.
Syntax of SQL Function REGEXP_INSTR
REGEXP_INSTR(string, pattern [, start_pos [, occurrence [, return_option [, match_param]]]])Example of SQL Function REGEXP_INSTR
SELECT REGEXP_INSTR('Oracle123SQL', '[0-9]+') AS number_pos
FROM dual;Returns position of first digit in the string.
25. SQL Function REGEXP_LIKE
Performs regular expression matching, like LIKE, but with more power and flexibility.
Use Cases of SQL Function REGEXP_LIKE
Validating formats like email, phone numbers, codes.
Syntax of REGEXP_LIKE
REGEXP_LIKE(string, pattern)Example of SQL Function REGEXP_LIKE
SELECT email
FROM users
WHERE REGEXP_LIKE(email, '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');Filters rows with valid email format.
26. SQL Function REGEXP_COUNT
Returns the number of times a pattern appears in a string.
Use Cases of SQL Function REGEXP_COUNT
Counting delimiters, matches, tokens, or specific patterns.
Syntax of SQL Function REGEXP_COUNT
REGEXP_COUNT(source_string, pattern [, position [, match_param]])Example of SQL Function REGEXP_COUNT
SELECT REGEXP_COUNT('aa12bb34cc56', '[0-9]+') AS count_numbers
FROM dual;Returns 3.
26. SQL Function ROUND
Rounds a number to a specified number of decimal places.
Use Cases of SQL Function ROUND
Rounding currency or scores in finance and billing applications.
Syntax of SQL Function ROUND
ROUND(number [, decimal_places])Example of SQL Function ROUND
SELECT ROUND(123.4567, 2) AS rounded_val FROM dual;Returns 123.46.
27. SQL Function TRUNC
Truncates a number (or date) to a specified number of decimal places or date unit.
Use Cases of SQL Function TRUNC
Removing time from datetime; truncating prices or values to integer form.
Syntax of SQL Function TRUNC (Numeric)
TRUNC(number [, decimal_places])Syntax of SQL Function TRUNC (Date)
TRUNC(date [, format])Example of SQL Function TRUNC
SELECT TRUNC(123.456, 2) AS trunc_val FROM dual;Returns 123.45
SELECT TRUNC(SYSDATE) AS trunc_date FROM dual;Returns current date with time set to midnight.
28. SQL Function MOD
Returns the remainder of a division operation.
Use Cases of SQL Function MOD
Identifying even/odd numbers; cyclic checks in inventory or scheduling.
Syntax of SQL Function MOD
MOD(number1, number2)Example of SQL Function MOD
SELECT MOD(10, 3) AS remainder FROM dual;Returns 1.
29. SQL Function ABS
Returns the absolute (non-negative) value of a number.
Use Cases of SQL Function ABS
Removing sign from results in variance or deviation calculations.
Syntax of SQL Function ABS
ABS(number)Example of SQL Function ABS
SELECT ABS(-150) AS abs_val FROM dual;Returns 150.
30. SQL Function CEIL (CEILING)
Returns the smallest integer greater than or equal to a number.
Use Cases of SQL Function CEIL
Estimating packaging requirements, invoice rounding, or rounding up transaction amounts.
Syntax of SQL Function CEIL
CEIL(number)Example of SQL Function CEIL
SELECT CEIL(15.2) AS ceil_val
FROM dual;Returns 16.
31. SQL Function FLOOR
Returns the largest integer less than or equal to a given number.
Use Cases of SQL Function FLOOR
Rounding down prices, discounts, or number-based groupings in billing systems.
Syntax of SQL Function FLOOR
FLOOR(number)Example of SQL Function FLOOR
SELECT FLOOR(15.78) AS floor_val
FROM dual;Returns 15.
32. SQL Function SIGN
Returns -1, 0, or 1 depending on the sign of the number.
Use Cases of SQL Function SIGN
Used in algorithms that rely on the direction of a change (positive, negative, or neutral).
Syntax of SQL Function SIGN
SIGN(number)Example of SIGN
SELECT SIGN(-45) AS sign_val FROM dual;Returns -1.
33. SQL Function POWER
Raises a number to the power of an exponent.
Use Cases of SQL Function POWER
Financial projections, scientific calculations.
Syntax of SQL Function POWER
POWER(base, exponent)Example of POWER
SELECT POWER(2, 3) AS result FROM dual;Returns 8.
34. SQL Function SQRT
Returns the square root of a number.
Use Cases of SQL Function SQRT
Statistical analysis; distance or geometric calculations.
Syntax of Oracle SQL Function SQRT
SQRT(number)Example of Oracle SQL Function SQRT
SELECT SQRT(49) AS result FROM dual;Returns 7.
35. Oracle SQL Function EXP
Returns the exponential value of a number (e to the power of x).
Use Cases of SQL Function EXP
Scientific calculations, growth modeling, exponential scoring.
Syntax of SQL Function EXP
EXP(number)Example of SQL Function EXP
SELECT EXP(1) AS e_val FROM dual;Returns 2.718… (Euler’s number).
36. Oracle SQL Function LOG
Returns the logarithm of a number for a specified base.
Use Cases of SQL Function LOG
When you need log values to custom bases (e.g., base 10, base 2) for analytics or data compression.
Syntax of SQL Function LOG
LOG(base, number)Example of SQL Function LOG
SELECT LOG(10, 1000) AS log_val FROM dual;Returns 3.
37. Oracle SQL Function LN
Returns the natural logarithm (base e) of a number.
Use Cases of SQL Function LN
Logarithmic scales, financial growth models, and data transformations.
Syntax of SQL Function LN
LN(number)Example of SQL Function LN
SELECT LN(10) AS log_result FROM dual;Returns natural log of 10.
38. Oracle SQL Function GREATEST
The GREATEST function returns the largest (maximum) value from a list of expressions. It compares two or more values of the same data type and returns the highest one. If any of the values is NULL, the result will be NULL (unless NULL is explicitly handled).
Use Cases of SQL Function GREATEST
✅ Find the maximum of multiple column values for a single row.
✅ Determine the latest date among several columns.
✅ Compare user inputs or calculations and select the best/highest value.
Syntax of SQL Function GREATEST
GREATEST(expr1, expr2, ..., exprN)- Each expr must be of a comparable data type (e.g., all numbers or all dates).
Example of SQL Function GREATEST
SELECT employee_name,
GREATEST(salary, bonus) AS max_payment
FROM employees;SELECT GREATEST(hire_date, review_date, sysdate) AS latest_event
FROM employee_reviews;🔍 Note:
If any input is NULL, the result is NULL unless you wrap values with NVL() or COALESCE() to avoid this behavior.
39. Oracle SQL Function LEAST
The LEAST function returns the smallest (minimum) value from a list of expressions. It is the counterpart to GREATEST and is used to identify the lowest value from multiple columns or expressions.
Use Cases of SQL Function LEAST
- ✅ Identify the lowest of several numeric scores or thresholds.
- ✅ Determine the earliest date among multiple columns.
- ✅ Apply constraints or limits based on minimal values.
Syntax of SQL Function LEAST
LEAST(expr1, expr2, ..., exprN)Example of SQL Function LEAST
SELECT customer_id, LEAST(max_credit_limit, current_balance) AS risk_threshold
FROM accounts;SELECT LEAST(start_date, deadline_date, sysdate) AS earliest_date
FROM project_schedule;🔍 Note:
Like GREATEST, LEAST returns NULL if any argument is NULL unless handled explicitly.
40. Oracle SQL Function SYSDATE
Returns the current system date and time.
Use Cases of SQL Function SYSDATE
Timestamping records, calculating durations, or filtering records based on current date.
Syntax of SQL Function SYSDATE
SYSDATEExample of SQL Function SYSDATE
SELECT SYSDATE FROM dual;Returns current date and time from the database server.
41. Oracle SQL Function CURRENT_DATE
Returns the current date in the session’s time zone. Unlike SYSDATE, which uses the database server’s time zone, this reflects the user’s time zone.
Use Cases of SQL Function CURRENT_DATE
Applications with global users needing local timestamps.
Syntax of SQL Function CURRENT_DATE
CURRENT_DATEExample of SQL Function CURRENT_DATE
SELECT CURRENT_DATE FROM dual;Returns the current date and time for the user session.
42. Oracle SQL Function SYSTIMESTAMP
Returns the system’s current timestamp with time zone and fractional seconds.
Use Cases of SQL Function SYSTIMESTAMP
Auditing, system-level logs, precise time records.
Syntax of SQL Function SYSTIMESTAMP
SYSTIMESTAMPExample of SQL Function SYSTIMESTAMP
SELECT SYSTIMESTAMP FROM dual;Returns: ‘2025-06-19 22:45:30.123456 +05:30’.
43. Oracle SQL Function LOCALTIMESTAMP
Returns the current timestamp without the time zone.
Use Cases of SQL Function LOCALTIMESTAMP
When timezone context is not needed, but time precision is.
Syntax of SQL Function LOCALTIMESTAMP
LOCALTIMESTAMPExample of SQL Function LOCALTIMESTAMP
SELECT LOCALTIMESTAMP AS local_time FROM dual;Shows timestamp based on local session setting.
44. Oracle SQL Function ADD_MONTHS
Adds or subtracts a number of months to/from a date.
Use Cases of SQL Function ADD_MONTHS
Calculating future billing dates, policy expiry, subscription renewal.
Syntax of SQL Function ADD_MONTHS
ADD_MONTHS(date, number_of_months)Example of SQL Function ADD_MONTHS
SELECT ADD_MONTHS(SYSDATE, 3) AS expiry_date FROM dual;Returns date 3 months from today.
45. Oracle SQL Function MONTHS_BETWEEN
Returns the number of months between two dates, including fractional months.
Use Cases of SQL Function MONTHS_BETWEEN
Age calculations, financial interest duration.
Syntax of SQL Function MONTHS_BETWEEN
MONTHS_BETWEEN(date1, date2)Example of SQL Function MONTHS_BETWEEN
SELECT MONTHS_BETWEEN(SYSDATE, TO_DATE('2024-06-01', 'YYYY-MM-DD')) AS months_diff FROM dual;Returns the number of months between two dates.
46. Oracle SQL Function NEXT_DAY
Returns the next specified weekday after a given date.
Use Cases of SQL Function NEXT_DAY
Scheduling tasks for the next Monday, Friday, etc.
Syntax of SQL Function NEXT_DAY
NEXT_DAY(date, 'DAY_OF_WEEK')Example of SQL Function NEXT_DAY
SELECT NEXT_DAY(SYSDATE, 'MONDAY') AS next_monday FROM dual;Returns the date of the upcoming Monday.
47. Oracle SQL Function LAST_DAY
Returns the last day of the month for a given date.
Use Cases of SQL Function LAST_DAY
End-of-month reports, salary payment scheduling.
Syntax of SQL Function LAST_DAY
LAST_DAY(date)Example of SQL Function LAST_DAY
ELECT LAST_DAY(SYSDATE) AS eom_date FROM dual;Returns the last day of the current month.
48. Oracle SQL Function TRUNC (DATE)
Truncates the time portion of a date or rounds it to the start of a specified unit (like month or year).
Use Cases of SQL Function
Comparing dates without time; grouping by month/year.
Syntax of SQL Function
TRUNC(date [, format])Example of Oracle SQL Function
SELECT TRUNC(SYSDATE, 'MM') AS month_start FROM dual;Returns the first day of the current month.
49. Oracle SQL Function ROUND (DATE)
Rounds a date to the nearest day, month, or specified unit.
Use Cases of SQL Function ROUND
Rounding invoice dates, adjusting for nearest reporting periods.
Syntax of SQL Function ROUND
ROUND(date [, format])Example of SQL Function ROUND
SELECT ROUND(TO_DATE('2025-06-19 12:00:00', 'YYYY-MM-DD HH24:MI:SS')) FROM dual;Returns 2025-06-20 (rounded up at noon).
50. Oracle SQL Function EXTRACT
Extracts a specific part (year, month, day, etc.) from a date or timestamp.
Use Cases of SQL Function EXTRACT
Creating year-wise reports, filtering by quarter/month.
Syntax of SQL Function EXTRACT
EXTRACT(part FROM date)Example of SQL Function EXTRACT
SELECT EXTRACT(YEAR FROM SYSDATE) AS current_year FROM dual;Returns current year as a number.
51. Oracle SQL Function TO_CHAR (Date/Number Conversion)
Converts date or number values into string format with optional formatting.
Use Cases of SQL Function TO_CHAR
Displaying formatted dates, currency, or numeric results.
Syntax of Oracle Function TO_CHAR
TO_CHAR(date, 'format')Example of SQL Function TO_CHAR
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') AS today FROM dual;Returns the current date as ‘2025-06-19’.
52. Oracle SQL Function TO_DATE
Converts a string into a date, using a specified format.
Use Cases of SQL Function TO_DATE
Converting user input or file input into valid Oracle dates.
Syntax of SQL Function TO_DATE
TO_DATE(string, 'format')Example of SQL Function TO_DATE
SELECT TO_DATE('2025-06-30', 'YYYY-MM-DD') AS valid_date FROM dual;Converts string to a DATE value.
53. Oracle SQL Function TO_NUMBER
Converts a string into a number using a specified format.
Use Cases of SQL Function TO_NUMBER
Parsing numeric input from string-based sources like forms or logs.
Syntax of SQL Function TO_NUMBER
TO_NUMBER(string [, 'format'])Example of SQL Function TO_NUMBER
SELECT TO_NUMBER('1,500.75', '9,999.99') AS numeric_val FROM dual;Returns 1500.75.
54. Oracle SQL Function CAST
Converts one data type to another (standard SQL-compliant). Works for number, date, varchar, etc.
Use Cases of SQL Function CAST
When writing portable SQL; converting values in views or subqueries.
Syntax of SQL Function CAST
CAST(expression AS target_type)Example of SQL Function CAST
SELECT CAST(123 AS VARCHAR2(10)) AS text_val FROM dual;Returns ‘123’ as a string.
55. Oracle SQL Function CONVERT
The CONVERT function in Oracle SQL is used to convert a string from one character set to another. This is particularly useful in multilingual applications, data migrations, or when exchanging data between Oracle databases configured with different character sets.
Use Cases of SQL Function CONVERT
✅ Convert legacy data (e.g., from WE8ISO8859P1) to a modern character set (e.g., AL32UTF8).
✅ Prevent mojibake (garbled characters) when exporting/importing multi-language content.
✅ Cleanse strings received from third-party systems with non-standard encoding.
Syntax of SQL Function CONVERT
CONVERT(string, to_charset [, from_charset])Example of SQL Function CONVERT
SELECT CONVERT('München', 'AL32UTF8', 'WE8ISO8859P1') AS utf8_name
FROM dual;This ensures characters like ü are correctly interpreted and stored in UTF-8.
56. Oracle SQL Function DUMP
Returns a VARCHAR2 value with datatype code, length in bytes, and internal representation.
Use Cases of SQL Function
Debugging encoding or datatype issues.
Syntax of SQL Function
DUMP(expr [, return_format [, start_position [, length]]])Example of SQL Function
SELECT DUMP('Oracle') AS dump_info FROM dual;Returns internal binary details of the string.
57. Oracle SQL Function BIN_TO_NUM
BIN_TO_NUM is a numeric function in Oracle SQL that converts a set of binary flags (0s and 1s) into a decimal (base-10) number. Each argument represents a binary digit (bit), and the function calculates the equivalent decimal number as if those digits were a binary number.
This is useful for bitmask operations, flag processing, and storing multiple yes/no options compactly in a single numeric value.
Use Cases of SQL Function BIN_TO_NUM
✅ Convert binary flag values (e.g., user permissions, feature toggles) into a single number for storage.
✅ Reverse bitmask logic by reconstructing a number from bitwise flags.
✅ Simulate binary representation in analytics or system-level reporting.
Syntax of SQL Function BIN_TO_NUM
BIN_TO_NUM(bit1, bit2, ..., bitN)Example of SQL Function BIN_TO_NUM
SELECT BIN_TO_NUM(1, 0, 1, 1) AS result
FROM dual;— Output: 11
1x8 + 0x4 + 1x2 + 1x1 = 11
58. Oracle SQL Function NUMTODSINTERVAL
Converts a number to an interval of days, hours, minutes, or seconds.
Use Cases of SQL Function NUMTODSINTERVAL
Adding time intervals dynamically to dates.
Syntax of SQL Function NUMTODSINTERVAL
NUMTODSINTERVAL(n, 'interval_unit')Example of SQL Function NUMTODSINTERVAL
SELECT SYSDATE + NUMTODSINTERVAL(2, 'DAY') AS future_date FROM dual;Adds 2 days to the current date.
59. Oracle SQL Function NUMTOYMINTERVAL
Converts a number to an interval of years or months.
Use Cases of SQL Function NUMTOYMINTERVAL
Working with duration in years or months when adjusting dates.
Syntax of SQL Function NUMTOYMINTERVAL
NUMTOYMINTERVAL(n, 'interval_unit')Example of SQL Function NUMTOYMINTERVAL
SELECT SYSDATE + NUMTOYMINTERVAL(1, 'YEAR') AS next_year FROM dual;Adds 1 year to today’s date.
60. Oracle SQL Function DECODE
Acts like a simple IF-ELSE logic by comparing an expression to multiple values and returning matching results.
Use Cases of SQL Function DECODE
Mapping department IDs to department names; converting status codes to readable strings.
Syntax of SQL Function DECODE
DECODE(expr, search1, result1 [, search2, result2, ...] [, default_result])
Example of SQL Function DECODE
SELECT DECODE(department_id, 10, 'Accounting', 20, 'HR', 30, 'IT', 'Other') AS dept_name
FROM employees;Maps department IDs to readable department names.
61. Oracle SQL Function CASE
Provides conditional logic in SQL queries, similar to IF-ELSE structures.
Use Cases of SQL Function CASE
Classifying data, implementing custom business logic in SELECT or WHERE clauses.
Syntax of SQL Function CASE
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
...
ELSE default_result
ENDExample of SQL Function CASE
SELECT employee_name,
CASE
WHEN salary > 10000 THEN 'High'
WHEN salary BETWEEN 5000 AND 10000 THEN 'Medium'
ELSE 'Low'
END AS salary_band
FROM employees;Categorizes employees based on salary.
62. Oracle SQL Function SUM
The SUM function adds up numbers in a column. It ignores any NULL values.
Use Cases of SQL Function SUM
- Calculate total sales
- Get total salary paid to employees
- Add up invoice amounts
- Syntax of Oracle SQL Function
Syntax of SQL Function SUM
SUM(column_name)Example of SQL Function SUM
SELECT SUM(salary) AS total_salary
FROM employees;✅ This query returns the total salary of all employees.
63. Oracle SQL Function AVG
The AVG function calculates the average of values in a column. NULLs are skipped.
Use Cases of SQL Function
- Average employee salary
- Average product price
- Average transaction value
Syntax of SQL Function AVG
AVG(column_name)Example of SQL Function AVG
SELECT AVG(salary) AS avg_salary
FROM employees;✅ This gives the average salary across all employees.
64. Oracle SQL Function MAX
The MAX function finds the largest value in a column. It works on numbers, dates, and text.
Use Cases of SQL Function
- Get the highest salary
- Show the most recent transaction
- Find the longest name
Syntax of SQL Function MAX
MAX(column_name)Example of SQL Function MAX
SELECT MAX(salary) AS highest_salary
FROM employees;✅ This query returns the highest salary.
65. Oracle SQL Function MIN
The MIN function finds the smallest value in a column. It works with numbers, dates, and text.
Use Cases of SQL Function MIN
- Find the earliest hire date
- Get the lowest salary
- Pick the first product alphabetically
Syntax of SQL Function MIN
MIN(column_name)Example of SQL Function MIN
SELECT MIN(hire_date) AS earliest_hire
FROM employees;✅ This shows the first hire date from the employees table.
66. Oracle SQL Function COUNT
The COUNT function returns the number of rows. It can count all rows or only non-null values in a column.
Use Cases of SQL Function COUNT
- Count total employees
- Count how many records have a value
- Count distinct customers
Syntax of SQL Function COUNT
COUNT(*)
COUNT(1)
COUNT(column_name)Example of SQL Function COUNT
SELECT COUNT(*) AS total_employees
FROM employees;SELECT COUNT(commission_pct) AS with_commission
FROM employees;✅ This counts employees who have a commission value.
67. Oracle SQL Function STDDEV
The STDDEV function returns the standard deviation of numeric values. It shows how much data varies from the average.
Use Cases of SQL Function STDDEV
- Measure sales consistency
- Analyze variation in salaries
- Monitor price fluctuation
Syntax of SQL Function STDDEV
STDDEV(column_name)Example of SQL Function STDDEV
SELECT STDDEV(salary) AS salary_deviation
FROM employees;✅ This shows how much the salary differs from the average.
68. Oracle SQL Function VARIANCE
The VARIANCE function measures the spread of numbers. It returns the statistical variance of numeric values.
Use Cases of SQL Function VARIANCE
- Financial data analysis
- Salary spread in departments
- Variance in product pricing
Syntax of SQL Function VARIANCE
VARIANCE(column_name)Example of SQL Function VARIANCE
SELECT VARIANCE(salary) AS salary_variance
FROM employees;✅ This shows how salaries are spread out in the organization.
69. Oracle SQL Function GROUPING
Distinguishes NULLs caused by grouping (i.e., summary rows in GROUP BY CUBE or ROLLUP).
Use Cases of SQL Function GROUPING
- Add labels like ‘Total’ in summary reports
- Distinguish between grouped data and subtotals when using ROLLUP or CUBE
Syntax of SQL Function GROUPING
GROUPING(column)Example of SQL Function GROUPING
SELECT department_id, job_id, SUM(salary),
GROUPING(department_id) AS dept_group
FROM employees
GROUP BY ROLLUP(department_id, job_id);Identifies subtotal or grand total rows.
70. Oracle SQL Function CUBE
Generates all possible subtotals and grand total combinations for specified columns.
Use Cases of SQL Function CUBE
Multidimensional data summarization (e.g., sales by year, product, and region).
Syntax of SQL Function CUBE
GROUP BY CUBE(column1, column2, ...)Example of SQL Function CUBE
SELECT department_id, job_id, SUM(salary)
FROM employees
GROUP BY CUBE(department_id, job_id);Includes all combinations of subtotals and grand total.
71. Oracle SQL Function ROLLUP
Produces group subtotals and grand totals by adding extra rows to a GROUP BY.
Use Cases of SQL Function ROLLUP
Reporting sales totals by region, with overall summary.
Syntax of SQL Function ROLLUP
GROUP BY ROLLUP(column1, column2, ...)Example of SQL Function ROLLUP
SELECT department_id, job_id, SUM(salary)
FROM employees
GROUP BY ROLLUP(department_id, job_id);Adds subtotals per department and grand total.
72. Oracle SQL Function RANK() OVER
Assigns a rank to each row in a result set with possible gaps in ranking for ties.
Use Cases of SQL Function RANK
Leaderboards, sales rankings, top performers.
Syntax of SQL Function RANK
RANK() OVER (ORDER BY column)Example of SQL Function RANK
SELECT employee_name, salary, RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;Employees with the same salary receive the same rank, next ranks are skipped.
73. Oracle SQL Function DENSE_RANK() OVER
Similar to RANK(), but does not skip ranks if there are ties.
Use Cases of SQL Function DENSE_RANK
Creating compact ranking lists where rank gaps are not desirable.
Syntax of SQL Function DENSE_RANK
DENSE_RANK() OVER (ORDER BY column)Example of SQL Function DENSE_RANK
SELECT employee_name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;No gaps in ranking even with duplicate values.
74. Oracle SQL Function ROW_NUMBER() OVER
Assigns a unique number to each row based on specified sort criteria, regardless of duplicates.
Use Cases of SQL Function ROW_NUMBER
Paginated queries, fetching top-N rows per group.
Syntax of SQL Function ROW_NUMBER
ROW_NUMBER() OVER (ORDER BY column)Example of SQL Function ROW_NUMBER
SELECT employee_name, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM employees;Returns unique row numbers starting from 1.
75. Oracle SQL Function NTILE() OVER
Divides a result set into a specified number of buckets (tiles), assigning each row a bucket number.
Use Cases of SQL Function NTILE
Quartile-based reports (top 25%, mid-range, etc.), percentile distribution.
Syntax of SQL Function NTILE
NTILE(n) OVER (ORDER BY column)Example of SQL Function NTILE
SELECT employee_name, salary, NTILE(4) OVER (ORDER BY salary DESC) AS salary_quartile
FROM employees;Divides employees into 4 salary-based groups.
76. Oracle SQL Function LAG() OVER
Accesses data from a previous row (offset) without self-joins.
Use Cases of SQL Function LAG
Comparing current vs. previous values (e.g., monthly sales difference).
Syntax of SQL Function LAG
LAG(column [, offset, default]) OVER (ORDER BY column)Example of SQL Function LAG
SELECT month, sales, LAG(sales, 1, 0) OVER (ORDER BY month) AS prev_month_sales
FROM sales_data;Shows previous month’s sales next to current.
77. Oracle SQL Function LEAD() OVER
Accesses data from a following row (offset).
Use Cases of SQL Function LEAD
Forecasting next period values, detecting changes
Syntax of SQL Function LEAD
LEAD(column [, offset, default]) OVER (ORDER BY column)Example of SQL Function LEAD
SELECT month, sales, LEAD(sales, 1, 0) OVER (ORDER BY month) AS next_month_sales
FROM sales_data;Shows next month’s sales beside the current.
78. Oracle SQL Function FIRST_VALUE() OVER
Returns the first value in an ordered partition of rows.
Use Cases of SQL Function FIRST_VALUE
Show the first sale of each region, or initial status.
Syntax of SQL Function FIRST_VALUE
FIRST_VALUE(column) OVER (ORDER BY column)Example of SQL Function FIRST_VALUE
SELECT department_id, employee_name, salary,
FIRST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY salary DESC) AS top_salary
FROM employees;Shows top salary per department.
79. Oracle SQL Function LAST_VALUE() OVER
Returns the last value in an ordered window.
Use Cases of SQL Function
Reporting trailing values, final status, last sale.
Syntax of Oracle SQL Function
LAST_VALUE(column) OVER (ORDER BY column ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)Example of SQL Function
SELECT department_id, employee_name, salary,
LAST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY salary DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS lowest_salary
FROM employees;Shows lowest salary per department.
80. Oracle SQL Function NTH_VALUE
The NTH_VALUE() function returns the nth value from a result set based on a specified sort order within a window or partition.
Use Cases of SQL Function NTH_VALUE
- Show the second or third highest salary
- Get the first and third products sold in each region
- Return the nth order date per customer
Syntax of SQL Function NTH_VALUE
NTH_VALUE(column_name, n)
OVER ([PARTITION BY expr] ORDER BY sort_expr [ROWS BETWEEN ...])Example of SQL Function NTH_VALUE
SELECT employee_id,
department_id,
salary,
NTH_VALUE(salary, 2) OVER (PARTITION BY department_id ORDER BY salary DESC) AS second_highest_salary
FROM employees;✅ This returns each employee’s salary and shows the second highest salary in their department.
81. Oracle SQL Function PERCENT_RANK() OVER
Returns the relative rank of a row as a percentage (0 to 1).
Use Cases of SQL Function PERCENT_RANK
Percentile comparisons in academic, statistical, or business data.
Syntax of SQL Function PERCENT_RANK
PERCENT_RANK() OVER (ORDER BY column)Example of SQL Function PERCENT_RANK
SELECT student_id, score, PERCENT_RANK() OVER (ORDER BY score DESC) AS score_percentile
FROM test_results;Calculates percentile rank for each score.
82. Oracle SQL Function CUME_DIST() OVER
Returns cumulative distribution (percent of rows ≤ current row).
Use Cases of SQL Function CUME_DIST
Benchmarking, percentile grouping, and progress thresholds.
Syntax of SQL Function CUME_DIST
CUME_DIST() OVER (ORDER BY column)Example of SQL Function CUME_DIST
SELECT employee_id, salary, CUME_DIST() OVER (ORDER BY salary) AS salary_percent
FROM employees;Returns the proportion of salaries less than or equal to the current one.
83. Oracle SQL Function UID
Returns the numeric ID of the currently logged-in user.
Use Cases of SQL Function UID
System-level logging, internal references.
Syntax of SQL Function UID
UIDExample of SQL Function UID
SELECT UID FROM dual;Returns an internal numeric ID of the session user.
84. Oracle SQL Function USER
Returns the name of the currently connected Oracle user.
Use Cases of SQL Function USER
Session tracking, auditing, multi-user reporting.
Syntax of SQL Function USER
USERExample of SQL Function USER
SELECT USER FROM dual;Returns the schema or username.
85. Oracle SQL Function USERENV
Returns information about the current session environment (like language, host, terminal).
Use Cases of SQL Function USERENV
Conditional logic based on user session, audits, and debugging.
Syntax of SQL Function USERENV
USERENVExample of Oracle SQL Function USERENV
SELECT USERENV('LANGUAGE') AS session_language FROM dual;Returns something like ‘AMERICAN_AMERICA.AL32UTF8’.
86. Oracle SQL Function VSIZE
Returns the number of bytes used by an expression (in memory).
Use Cases of SQL Function VSIZE
Storage analysis, space optimization.
Syntax of SQL Function VSIZE
VSIZE(expr)Example of SQL Function VSIZE
SELECT VSIZE('Oracle') AS size_bytes FROM dual;Returns 6.
87. Oracle SQL Function SYS_CONTEXT
Retrieves session-related information from a specified namespace.
Use Cases of SQL Function SYS_CONTEXT
Tracking IP, client details, application name during session.
Syntax of SQL Function SYS_CONTEXT
SYS_CONTEXT('namespace', 'parameter')Example of SQL Function SYS_CONTEXT
SELECT SYS_CONTEXT('USERENV', 'IP_ADDRESS') AS client_ip FROM dual;Returns the client IP address if available.
88. Oracle SQL Function ORA_HASH
ORA_HASH generates a hash value for a given input, typically used to distribute, group, or partition data efficiently. It’s a deterministic hash function — the same input always yields the same output — and is commonly used for:
- Load balancing across partitions.
- Fast equality comparisons.
- Simulating hash joins or hash buckets.
It works on many data types, including strings, numbers, and dates.
Use Cases of SQL Function ORA_HASH
- ✅ Distribute rows uniformly across hash partitions or parallel processes.
- ✅ Create fast lookup structures (like hash buckets) for comparison operations.
- ✅ Group large volumes of data without scanning entire values.
- ✅ Simulate consistent hashing logic for ETL or data warehousing.
Syntax of SQL Function ORA_HASH
ORA_HASH(expr [, max_bucket [, seed_value]])- expr: The expression to hash (string, number, etc.).
- max_bucket (optional): Highest hash bucket value. Result will be between 0 and this number.
- seed_value (optional): Number to initialize the hash calculation for custom behavior.
Example of SQL Function ORA_HASH
SELECT employee_id, ORA_HASH(employee_id, 9) AS bucket
FROM employees;
-- Returns value between 0 and 9
- ORA_HASH is non-cryptographic. It is not meant for data security or password hashing.
- It’s fast and lightweight, suitable for large-scale grouping and comparisons.
- Returns NULL if expr is NULL.
89. Oracle SQL Function SYS_GUID
Generates a globally unique identifier in RAW(16) format.
Use Cases of SQL Function
Creating primary keys, tracking unique events across systems.
Syntax of SQL Function
SYS_GUID()Example of SQL Function
SELECT SYS_GUID() AS unique_id FROM dual;Returns a unique RAW value for each row.
90. Oracle SQL Function JSON_OBJECT
JSON_OBJECT constructs a well-formed JSON object from column values or expressions. It’s used to generate JSON directly from SQL queries, useful in APIs, reports, or when storing structured data as JSON.
Use Cases of SQL Function JSON_OBJECT
- ✅ Generate JSON output from relational rows.
- ✅ Build JSON-based APIs directly from SQL.
- ✅ Insert structured JSON into CLOB or BLOB columns.
Syntax of SQL Function JSON_OBJECT
JSON_OBJECT(key1 VALUE expr1 [, key2 VALUE expr2, ...])Example of SQL Function JSON_OBJECT
SELECT JSON_OBJECT('name' VALUE first_name, 'salary' VALUE salary)
FROM employees
WHERE employee_id = 101;{"name":"John","salary":6000}
91. Oracle SQL Function JSON_VALUE
JSON_VALUE extracts a scalar value (string, number, Boolean, etc.) from a JSON-formatted string stored in a column or expression.
Use Cases of SQL Function JSON_VALUE
- ✅ Query a specific value from stored JSON.
- ✅ Use JSON fields in WHERE or SELECT.
- ✅ Filter data based on JSON attributes.
Syntax of SQL Function JSON_VALUE
JSON_VALUE(json_column, '$.path')Example of SQL Function JSON_VALUE
SELECT JSON_VALUE(employee_data, '$.address.city') AS city
FROM employee_json
WHERE department_id = 10;92. Oracle SQL Function JSON_TABLE
JSON_TABLE maps JSON data into relational rows and columns. It acts like a virtual table derived from a JSON document, enabling joins and aggregation.
Use Cases of SQL Function JSON_TABLE
- ✅ Flatten nested JSON for reporting.
- ✅ Query JSON as if it were a table.
- ✅ Join JSON data with relational tables.
Syntax of SQL Function JSON_TABLE
JSON_TABLE(json_column, '$.path'
COLUMNS (
column1 PATH '$.key1',
column2 PATH '$.key2'
)
)Example of SQL Function JSON_TABLE
SELECT jt.*
FROM departments d,
JSON_TABLE(d.employee_json, '$.employees[*]'
COLUMNS (
emp_name VARCHAR2(50) PATH '$.name',
emp_role VARCHAR2(50) PATH '$.role'
)
) jt;93. Oracle SQL Function XMLAGG
XMLAGG aggregates XML fragments into a single XML document. It is used to concatenate XML-formatted rows, commonly in XML reports or legacy integrations.
Use Cases of SQL Function XMLAGG
- ✅ Concatenate multiple XML rows into one.
- ✅ Generate XML documents from query output.
- ✅ Use with
XMLELEMENTfor structured XML output.
Syntax of SQL Function XMLAGG
XMLAGG(XMLELEMENT("tag", column)) [ORDER BY clause]Example of SQL Function XMLAGG
SELECT XMLAGG(XMLELEMENT("employee", first_name) ORDER BY first_name) AS xml_result
FROM employees;94. Oracle SQL Function PIVOT
PIVOT transforms rows into columns, allowing you to rotate data for better readability or matrix-style analysis. It is especially useful in reports.
Use Cases of SQL Function PIVOT
- ✅ Convert row data into columnar summaries.
- ✅ Create cross-tab reports (e.g., monthly totals).
- ✅ Summarize values across categories.
Syntax of SQL Function PIVOT
SELECT * FROM (
SELECT column1, column2, value
FROM your_table
)
PIVOT (
AGG_FUNCTION(value)
FOR column2 IN ('VAL1' AS col1, 'VAL2' AS col2)
);Example of SQL Function PIVOT
SELECT *
FROM (
SELECT dept_id, month, salary
FROM payroll
)
PIVOT (
SUM(salary)
FOR month IN ('JAN' AS Jan, 'FEB' AS Feb, 'MAR' AS Mar)
);95. Oracle SQL Function UNPIVOT
UNPIVOT rotates columns into rows, essentially reversing the effect of PIVOT. It is used to normalize denormalized data for querying or storage.
Use Cases of SQL Function UNPIVOT
- ✅ Reshape wide tables into vertical formats.
- ✅ Prepare data for analytics or ETL.
- ✅ Convert columnar time-series data into rows.
Syntax of SQL Function UNPIVOT
SELECT * FROM your_table
UNPIVOT (
value FOR column_name IN (col1 AS 'VAL1', col2 AS 'VAL2')
);Example of SQL Function UNPIVOT
SELECT *
FROM monthly_salaries
UNPIVOT (
salary FOR month IN (jan AS 'JAN', feb AS 'FEB', mar AS 'MAR')
);Final Thoughts
This extensive list provides a powerful toolkit for anyone working with SQL. By understanding and effectively utilizing these 100 functions, you can significantly enhance your ability to query, transform, analyze, and manage data efficiently across various database platforms. Remember that while many functions are standardized, some have database-specific implementations or variations, so always consult your database’s official documentation for precise syntax and behavior. Practice these functions regularly to unlock the full potential of your SQL queries and become a more proficient data professional.
Pingback: Top 10 SQL Query Every Everybody Should Know in 2025
Pingback: Oracle LISTAGG Function: Turning Rows into a Single String