Single-Row Functions (Scalar Functions)
Single-row functions operate on a single row and return a single result. They can be used in SELECT, WHERE, ORDER BY, and HAVING clauses. These functions are crucial for data manipulation at a granular level, allowing precise control over how data is presented and processed.
Character Functions: UPPER, LOWER, INITCAP, LENGTH, SUBSTR, INSTR, CONCAT, TRIM, LPAD, RPAD, REPLACE
Character functions are used to manipulate character strings. They are vital for standardizing data, extracting information, and preparing text for display or further processing.
Example 1: UPPER, LOWER, INITCAP
SELECT
'oracle database' AS original_string, -- Original string for demonstration
UPPER('oracle database') AS upper_case, -- Converts the string to all uppercase
LOWER('Oracle Database') AS lower_case, -- Converts the string to all lowercase
INITCAP('oracle database') AS init_cap -- Capitalizes the first letter of each word
FROM
dual;
Explanation This SQL query demonstrates the use of UPPER, LOWER, and INITCAP functions. UPPER converts all characters in a string to uppercase. LOWER converts all characters to lowercase. INITCAP converts the first letter of each word in a string to uppercase and the rest to lowercase, commonly used for proper capitalization of names or titles. These functions are frequently used for data cleansing and presentation.
Example 2: LENGTH, SUBSTR
SELECT
'Oracle 19c SQL' AS original_string, -- The original string
LENGTH('Oracle 19c SQL') AS string_length, -- Returns the length of the string
SUBSTR('Oracle 19c SQL', 8, 3) AS extracted_substring, -- Extracts '19c' starting from position 8 for 3 characters
SUBSTR('Oracle 19c SQL', -3) AS last_three_chars -- Extracts 'SQL' by starting from the 3rd character from the end
FROM
dual;
Explanation This example showcases LENGTH and SUBSTR. LENGTH returns the number of characters in a string. SUBSTR extracts a substring from a string. It takes three arguments: the string, the starting position, and the length. A negative starting position indicates counting from the end of the string. These functions are commonly used for data parsing and extracting specific parts of text data.
Example 3: INSTR, CONCAT
SELECT
'Oracle Database' AS original_string, -- Original string
INSTR('Oracle Database', 'Data') AS position_of_data, -- Finds the starting position of 'Data'
INSTR('Oracle Database', 'x') AS position_of_x, -- Returns 0 if substring is not found
CONCAT('Oracle', '19c') AS concatenated_string_1, -- Concatenates two strings
'Oracle' || ' ' || '19c' AS concatenated_string_2 -- Another way to concatenate strings using || operator
FROM
dual;
Explanation This query illustrates INSTR and CONCAT. INSTR returns the starting position of a specified substring within a string. If the substring is not found, it returns 0. CONCAT joins two strings together. The || operator provides a more flexible way to concatenate multiple strings and is often preferred for readability. These are useful for searching within strings and combining data from different columns.
Example 4: TRIM, LPAD, RPAD
SELECT
' Hello World ' AS original_string, -- String with leading/trailing spaces
TRIM(' Hello World ') AS trimmed_string, -- Removes both leading and trailing spaces
LPAD('SQL', 10, '*') AS left_padded_string, -- Pads 'SQL' on the left with '*' to a total length of 10
RPAD('SQL', 10, '-') AS right_padded_string -- Pads 'SQL' on the right with '-' to a total length of 10
FROM
dual;
Explanation This example demonstrates TRIM, LPAD, and RPAD. TRIM removes leading and trailing spaces (or specified characters) from a string. LPAD (Left Pad) and RPAD (Right Pad) add characters to the left or right of a string, respectively, until it reaches a specified total length. These functions are invaluable for formatting output, aligning text, and cleaning up input data.
Example 5: REPLACE
SELECT
'Oracle 19c Database' AS original_string, -- The original string
REPLACE('Oracle 19c Database', '19c', '21c') AS replaced_string_version, -- Replaces '19c' with '21c'
REPLACE('Hello World', 'o', 'x') AS replaced_char -- Replaces all occurrences of 'o' with 'x'
FROM
dual;
Explanation The REPLACE function is shown in this query. It replaces all occurrences of a specified substring within a string with another specified substring. This function is extremely useful for data standardization, correcting typos, and updating specific parts of text data across multiple records.
Number Functions: ROUND, TRUNC, MOD, ABS, SQRT, POWER, CEIL, FLOOR
Number functions perform mathematical operations on numeric data. They are crucial for calculations, rounding, and extracting specific parts of numbers.
Example 1: ROUND, TRUNC
SELECT
123.456 AS original_number, -- Original number for demonstration
ROUND(123.456) AS rounded_to_nearest_int, -- Rounds to the nearest integer (123)
ROUND(123.456, 2) AS rounded_to_two_decimals, -- Rounds to two decimal places (123.46)
TRUNC(123.789) AS truncated_to_int, -- Truncates to the nearest integer (123)
TRUNC(123.789, 1) AS truncated_to_one_decimal -- Truncates to one decimal place (123.7)
FROM
dual;
Explanation This example demonstrates ROUND and TRUNC. ROUND rounds a number to a specified number of decimal places or to the nearest integer if no decimal places are specified. TRUNC truncates a number to a specified number of decimal places, effectively removing the fractional part beyond that point without rounding. These are frequently used in financial calculations and data presentation.
Example 2: MOD, ABS
SELECT
10 AS dividend, -- The number to be divided
3 AS divisor, -- The number by which to divide
MOD(10, 3) AS remainder_of_division, -- Returns the remainder of 10 divided by 3 (1)
-5 AS negative_number, -- A negative number
ABS(-5) AS absolute_value_of_neg_5, -- Returns the absolute value (5)
ABS(5) AS absolute_value_of_pos_5 -- Returns the absolute value (5)
FROM
dual;
Explanation This query illustrates MOD and ABS. MOD returns the remainder of a division operation, useful for tasks like checking for even/odd numbers or cyclical operations. ABS returns the absolute (non-negative) value of a number, widely used when the magnitude of a number is more important than its sign.
Example 3: SQRT, POWER
SELECT
25 AS number_for_sqrt, -- Number to find the square root of
SQRT(25) AS square_root_of_25, -- Returns the square root (5)
2 AS base_number, -- Base for the power function
3 AS exponent, -- Exponent for the power function
POWER(2, 3) AS two_to_the_power_of_three -- Returns 2 raised to the power of 3 (8)
FROM
dual;
Explanation This example showcases SQRT and POWER. SQRT calculates the square root of a non-negative number. POWER raises a base number to a specified exponent. These functions are essential for complex mathematical and scientific calculations within SQL.
Example 4: CEIL, FLOOR
SELECT
123.456 AS decimal_number_1, -- First decimal number
123.789 AS decimal_number_2, -- Second decimal number
CEIL(123.456) AS ceil_value_1, -- Returns the smallest integer greater than or equal to 123.456 (124)
CEIL(123.789) AS ceil_value_2, -- Returns the smallest integer greater than or equal to 123.789 (124)
FLOOR(123.456) AS floor_value_1, -- Returns the largest integer less than or equal to 123.456 (123)
FLOOR(123.789) AS floor_value_2 -- Returns the largest integer less than or equal to 123.789 (123)
FROM
dual;
Explanation This query demonstrates CEIL and FLOOR. CEIL (Ceiling) returns the smallest integer greater than or equal to the given number. FLOOR returns the largest integer less than or equal to the given number. These functions are useful when you need to round numbers up or down to the nearest whole number, regardless of the decimal value, often used in inventory or capacity planning.
Example 5: Mixed Number Functions
SELECT
POWER(ABS(TRUNC(-5.7)), 2) AS calculated_value -- Calculates (absolute value of truncated -5.7) squared
FROM
dual;
Explanation This example combines POWER, ABS, and TRUNC. It first truncates -5.7 to -5, then takes its absolute value (5), and finally raises 5 to the power of 2, resulting in 25. This demonstrates how multiple number functions can be nested to perform complex calculations in a single SQL statement.
Date and Time Functions: SYSDATE, CURRENT_DATE, CURRENT_TIMESTAMP, ADD_MONTHS, MONTHS_BETWEEN, NEXT_DAY, LAST_DAY, EXTRACT, TO_CHAR for dates, TO_DATE for strings
Date and time functions are essential for working with temporal data, enabling calculations, formatting, and extraction of specific date and time components.
Example 1: SYSDATE, CURRENT_DATE, CURRENT_TIMESTAMP
SELECT
SYSDATE AS current_system_date_time, -- Returns the current date and time from the database server
CURRENT_DATE AS current_session_date, -- Returns the current date in the session's time zone
CURRENT_TIMESTAMP AS current_session_timestamp -- Returns the current date and time with timezone of the session
FROM
dual;
Explanation This example shows different ways to get the current date and time. SYSDATE returns the date and time from the database server. CURRENT_DATE returns the current date in the session's time zone. CURRENT_TIMESTAMP returns the current date and time, including fractional seconds and the session's time zone. Understanding these distinctions is crucial for accurate date and time handling across different time zones.
Example 2: ADD_MONTHS, MONTHS_BETWEEN
SELECT
SYSDATE AS today, -- Current date
ADD_MONTHS(SYSDATE, 3) AS date_plus_3_months, -- Adds 3 months to the current date
ADD_MONTHS(SYSDATE, -1) AS date_minus_1_month, -- Subtracts 1 month from the current date
MONTHS_BETWEEN(SYSDATE, SYSDATE - 90) AS months_diff -- Calculates the number of months between two dates
FROM
dual;
Explanation This query demonstrates ADD_MONTHS and MONTHS_BETWEEN. ADD_MONTHS adds or subtracts a specified number of months from a date. MONTHS_BETWEEN calculates the number of months between two dates, returning a fractional number if days are involved. These functions are frequently used in financial calculations, project scheduling, and age calculations.
Example 3: NEXT_DAY, LAST_DAY
SELECT
SYSDATE AS today, -- Current date
NEXT_DAY(SYSDATE, 'MONDAY') AS next_monday, -- Returns the date of the next Monday after today
LAST_DAY(SYSDATE) AS last_day_of_current_month, -- Returns the last day of the current month
LAST_DAY(ADD_MONTHS(SYSDATE, 1)) AS last_day_of_next_month -- Returns the last day of the next month
FROM
dual;
Explanation This example illustrates NEXT_DAY and LAST_DAY. NEXT_DAY returns the date of the first specified day of the week that is later than the given date. LAST_DAY returns the date of the last day of the month for a given date. These are helpful for scheduling, reporting, and calendar-related calculations.
Example 4: EXTRACT, TO_CHAR for dates
SELECT
SYSDATE AS today, -- Current date
EXTRACT(YEAR FROM SYSDATE) AS current_year, -- Extracts the year component
EXTRACT(MONTH FROM SYSDATE) AS current_month, -- Extracts the month component
EXTRACT(DAY FROM SYSDATE) AS current_day, -- Extracts the day component
TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS formatted_datetime, -- Formats date and time
TO_CHAR(SYSDATE, 'DD-MON-YYYY') AS formatted_date -- Formats date in a specific way
FROM
dual;
Explanation This query shows EXTRACT and TO_CHAR for dates. EXTRACT retrieves specific components (year, month, day, hour, minute, second) from a date or timestamp. TO_CHAR converts a date or timestamp into a character string, allowing for custom formatting using various format models. This is essential for presenting date and time information in user-friendly formats.
Example 5: TO_DATE for strings
SELECT
TO_DATE('15-JUN-2025', 'DD-MON-YYYY') AS date_from_string_1, -- Converts string to date
TO_DATE('2025/06/15 10:30:00', 'YYYY/MM/DD HH24:MI:SS') AS date_from_string_2, -- Converts string with time to date
TO_DATE('06/15/25', 'MM/DD/RR') AS date_from_string_3 -- Converts string using RR format for two-digit years
FROM
dual;
Explanation This example demonstrates TO_DATE. TO_DATE converts a character string into a date value. It requires a format model that matches the format of the input string. This function is crucial for inserting or updating date columns from string inputs and performing date-based comparisons.
Conversion Functions: TO_CHAR (Number to String), TO_NUMBER (String to Number), TO_DATE (String to Date), CAST
Conversion functions change data from one datatype to another. These are vital for ensuring data compatibility in operations, allowing you to seamlessly work with different data types.
Example 1: TO_CHAR (Number to String)
SELECT
12345.67 AS original_number, -- Numeric value
TO_CHAR(12345.67) AS number_to_string_default, -- Converts number to string with default formatting
TO_CHAR(12345.67, '99,999.00') AS number_to_string_formatted, -- Converts with comma separator and two decimal places
TO_CHAR(12345.67, '$99,999.00') AS currency_formatted_string -- Converts to currency format
FROM
dual;
Explanation This example shows TO_CHAR converting numbers to strings. It allows you to format numeric output with specific separators, decimal places, and currency symbols. This is extremely useful for generating reports and displays where numerical data needs to be presented in a specific, readable format.
Example 2: TO_NUMBER (String to Number)
SELECT
'12345' AS original_string_1, -- Numeric string
TO_NUMBER('12345') AS string_to_number_plain, -- Converts a string to a number
'1,234.56' AS original_string_2, -- Numeric string with formatting
TO_NUMBER('1,234.56', '9,999.99') AS string_to_number_formatted -- Converts string with format mask
FROM
dual;
Explanation This query demonstrates TO_NUMBER. It converts a character string containing numeric characters into a number. If the string contains formatting characters (like commas or currency symbols), a format model must be provided that matches the string's format. This is crucial for performing calculations on numeric data that is stored or input as strings.
Example 3: TO_DATE (String to Date)
SELECT
'2025-06-15' AS original_string_1, -- Date string
TO_DATE('2025-06-15', 'YYYY-MM-DD') AS string_to_date_format_1, -- Converts string to date
'June 15, 2025' AS original_string_2, -- Another date string format
TO_DATE('June 15, 2025', 'Month DD, YYYY') AS string_to_date_format_2 -- Converts string to date with different format
FROM
dual;
Explanation This example shows TO_DATE converting strings to date values. As seen earlier, a format model is required to correctly interpret the string's date format. This function is fundamental for data loading, comparisons, and any operations requiring date arithmetic where the input is a character string.
Example 4: CAST
SELECT
'123' AS original_string, -- A string
CAST('123' AS NUMBER) AS cast_to_number, -- Converts string to number
SYSDATE AS current_date_time, -- Current date and time
CAST(SYSDATE AS TIMESTAMP) AS cast_to_timestamp, -- Converts date to timestamp
123.456 AS original_number, -- A number
CAST(123.456 AS VARCHAR2(10)) AS cast_to_varchar2 -- Converts number to varchar2
FROM
dual;
Explanation The CAST function is demonstrated here. It converts a value from one datatype to another. CAST is more general than TO_CHAR, TO_NUMBER, and TO_DATE as it can convert between a wider range of data types, including numeric, character, date/time, and binary types. It provides a standard SQL way to perform explicit type conversions.
Example 5: Mixed Conversion Functions
SELECT
TO_NUMBER(TO_CHAR(SYSDATE, 'YYYYMMDD')) AS date_to_number_format, -- Converts current date to a number (e.g., 20250615)
CAST(CAST('2025-06-15' AS DATE) AS VARCHAR2(20)) AS string_to_date_to_string -- Converts string to date then back to string
FROM
dual;
Explanation This example shows nested conversion functions. The first expression converts SYSDATE to a character string in 'YYYYMMDD' format, then converts that string to a number. The second converts a string to a date, and then CAST converts that date back into a VARCHAR2 string. This illustrates the flexibility and power of combining conversion functions for complex data transformations.
General Functions: NVL, NVL2, COALESCE, DECODE
These general functions handle null values and provide conditional logic within SQL queries. They are crucial for robust data handling and ensuring that queries behave predictably even when data is missing.
Example 1: NVL
SELECT
employee_id, -- Employee ID
NVL(commission_pct, 0) AS actual_commission_pct -- Replaces NULL commission_pct with 0
FROM
employees; -- Assuming an 'employees' table with 'commission_pct' column
Explanation The NVL (Null Value Logic) function replaces a null value with a specified alternative value. In this example, if an employee's commission_pct is NULL, it will be displayed as 0. This is very common for calculations where NULL would otherwise lead to an unknown result.
Example 2: NVL2
SELECT
employee_id, -- Employee ID
salary, -- Employee's salary
NVL2(commission_pct, salary * (1 + commission_pct), salary) AS total_compensation -- If commission_pct is not NULL, calculate total; otherwise, use just salary
FROM
employees;
Explanation NVL2 is an extension of NVL. It evaluates an expression: if the expression is not null, it returns a second specified value; if it is null, it returns a third specified value. Here, if commission_pct is not null, it calculates salary * (1 + commission_pct); otherwise, it simply returns salary. This provides more sophisticated null handling logic.
Example 3: COALESCE
SELECT
product_id, -- Product ID
COALESCE(warehouse_location, store_location, default_location, 'Unknown') AS preferred_location -- Returns the first non-null expression from the list
FROM
product_locations; -- Assuming a 'product_locations' table
Explanation COALESCE returns the first non-null expression in a list of expressions. It is highly useful for defining a hierarchy of fallback values. In this example, it tries warehouse_location, then store_location, then default_location, and if all are null, it defaults to 'Unknown'. COALESCE is a standard SQL function and is more flexible than NVL for multiple possible null values.
Example 4: DECODE
SELECT
job_id, -- Job ID
DECODE(job_id,
'AD_PRES', 'President', -- If job_id is 'AD_PRES', return 'President'
'IT_PROG', 'Programmer', -- If job_id is 'IT_PROG', return 'Programmer'
'SA_REP', 'Sales Representative', -- If job_id is 'SA_REP', return 'Sales Representative'
'Unknown Job' -- Default value if no match is found
) AS job_title
FROM
employees;
Explanation DECODE is a powerful Oracle-specific function for conditional logic, similar to a CASE statement. It compares an expression to a series of search values and returns the corresponding result value. If no match is found, it returns the optional default value. It's often used for simple lookups or to translate coded values into more readable descriptions.
Example 5: Mixed General Functions
SELECT
employee_id, -- Employee ID
COALESCE(NVL(phone_number, 'N/A'), 'No Contact Info') AS contact_info -- Combines NVL and COALESCE
FROM
employees;
Explanation This example demonstrates a combination of NVL and COALESCE. NVL(phone_number, 'N/A') ensures that if phone_number is null, it defaults to 'N/A'. Then, COALESCE is used on that result. If NVL(phone_number, 'N/A') itself results in a null (which won't happen if 'N/A' is never null, but conceptually demonstrates nesting), or if there were other columns, it would then fall back to 'No Contact Info'. This showcases how these functions can be nested for complex null handling and conditional logic.
Conditional Expressions: CASE Statement and CASE Expression
CASE expressions provide powerful conditional logic within SQL queries, allowing you to return different values based on specified conditions. They are a more flexible and standard SQL alternative to DECODE.
Example 1: Simple CASE Expression
SELECT
product_name, -- Product name
price, -- Product price
CASE category_id
WHEN 1 THEN 'Electronics' -- If category_id is 1, it's 'Electronics'
WHEN 2 THEN 'Books' -- If category_id is 2, it's 'Books'
WHEN 3 THEN 'Clothing' -- If category_id is 3, it's 'Clothing'
ELSE 'Other' -- For any other category_id
END AS product_category
FROM
products; -- Assuming a 'products' table with 'category_id'
Explanation This is a simple CASE expression. It compares category_id to a series of specific values and returns a corresponding string. The ELSE clause is optional but highly recommended to handle cases that don't match any of the WHEN conditions. This is perfect for transforming coded values into more meaningful descriptions.
Example 2: Searched CASE Expression
SELECT
employee_id, -- Employee ID
salary, -- Employee's salary
CASE
WHEN salary < 3000 THEN 'Low' -- If salary is less than 3000
WHEN salary BETWEEN 3000 AND 8000 THEN 'Medium' -- If salary is between 3000 and 8000
WHEN salary > 8000 THEN 'High' -- If salary is greater than 8000
ELSE 'Undefined' -- Catch-all for any other case (e.g., NULL salary)
END AS salary_level
FROM
employees;
Explanation This is a searched CASE expression, where each WHEN clause contains a boolean condition. It evaluates conditions sequentially and returns the result of the first true condition. This type of CASE is more flexible than the simple CASE as it allows for more complex comparisons and ranges.
Example 3: CASE with Aggregate Functions
SELECT
SUM(CASE WHEN order_status = 'SHIPPED' THEN 1 ELSE 0 END) AS shipped_orders, -- Counts shipped orders
SUM(CASE WHEN order_status = 'PENDING' THEN 1 ELSE 0 END) AS pending_orders, -- Counts pending orders
COUNT(CASE WHEN order_status = 'CANCELLED' THEN 1 END) AS cancelled_orders -- Counts cancelled orders (NULLs not counted by COUNT)
FROM
orders; -- Assuming an 'orders' table with 'order_status'
Explanation CASE expressions can be powerfully combined with aggregate functions. Here, SUM counts specific order statuses by assigning 1 if the condition is met and 0 otherwise. COUNT can also be used, where it only counts non-null values (so assigning 1 for true and null for false works). This pattern is often used for creating pivot-like reports or conditional aggregations.
Example 4: CASE for Ordering Results
SELECT
product_name, -- Product name
price, -- Product price
CASE
WHEN product_name LIKE '%Oracle%' THEN 1 -- Oracle products first
WHEN product_name LIKE '%SQL%' THEN 2 -- SQL products second
ELSE 3 -- Others last
END AS sort_order
FROM
products
ORDER BY
sort_order, product_name; -- Orders by the CASE result, then product name
Explanation CASE expressions can be used within the ORDER BY clause to define custom sorting logic. This allows you to prioritize specific rows or groups of rows based on conditions that are not directly supported by simple ascending/descending sorts. Here, products containing 'Oracle' come first, then 'SQL', then all others.
Example 5: Nested CASE Expressions
SELECT
customer_id, -- Customer ID
CASE
WHEN total_purchases > 1000 THEN
CASE
WHEN customer_segment = 'VIP' THEN 'Premium VIP' -- Nested condition
ELSE 'High Value Customer'
END
WHEN total_purchases BETWEEN 500 AND 1000 THEN 'Medium Value Customer'
ELSE 'Standard Customer'
END AS customer_tier
FROM
customers; -- Assuming a 'customers' table
Explanation CASE expressions can be nested, allowing for complex, multi-level conditional logic. In this example, if total_purchases are over 1000, it then checks the customer_segment to further categorize the customer. Nested CASE statements provide granular control over data classification and are useful for intricate business rules.