Lesson 1

Data stored in a database is not always presented in the form an application, report, or user needs. A customer address may occupy several columns, names may use inconsistent capitalization, monetary values may require rounding, and datetime values may need a clear display format. Oracle SQL functions let a query transform, examine, convert, or derive values while the database processes the statement.

In a SELECT statement, a function normally changes the value returned by an expression; it does not change the value stored in the source table. For example, displaying a last name through UPPER produces uppercase text in the result, but it does not rewrite the column. Data is persistently changed only when a separate data manipulation statement writes a value to the table.

This module concentrates on frequently used single-row functions for character, numeric, datetime, null, and conditional expressions in Oracle AI Database 26ai. Later lessons examine individual functions in detail. This introduction establishes a framework for choosing and combining them safely.

By the end of this module, you will be able to:

1. Explain how single-row functions transform values without automatically changing stored source data.
2. Combine, measure, locate, extract, trim, and change the case of character values.
3. Distinguish character-counting functions from byte-counting and Unicode-aware variants.
4. Apply numeric ROUND and TRUNC with the intended precision.
5. Format numeric and datetime output explicitly with TO_CHAR.
6. Distinguish an Oracle datetime value from its formatted character representation.
7. Select current-date and current-time functions with awareness of database-host and session time-zone context.
8. Handle null values with NVL and recognize when COALESCE is a clearer alternative.
9. Use DECODE correctly and recognize when a CASE expression better communicates conditional logic.

A SQL function accepts zero or more arguments and returns a result. Its general form is function_name(argument1, argument2, ...). An argument can be a literal, a column, an arithmetic expression, another function call, or another expression that is valid at that point in the SQL statement. Functions can be nested, so the result of an inner function becomes an argument to an outer function.

Built-in SQL functions are part of Oracle SQL; they are not simply built-in PL/SQL programs. Oracle also permits eligible user-defined functions to be called from SQL, but defining those routines is outside this module. Here, the important skill is understanding each function's accepted arguments, return datatype, null behavior, and dependence on session or globalization settings.

A function can appear in a select list and, when its semantics permit, in expressions used by clauses such as WHERE, ORDER BY, GROUP BY, and HAVING. A descriptive column alias should accompany a calculated expression so that its meaning is apparent in the result.

The statement that a function “returns one value” needs context. A function invocation returns a result, but the number of result rows is determined by the query and the class of function being used.

Function class	Data considered	Result behavior	Role in this module
Single-row	Expression values from one input row	Returns one result for each row processed	Primary focus
Aggregate	A group of rows or the entire selected set	Returns one result for each group	Boundary concept
Analytic	A window of rows related to the current result row	Calculates across the window while retaining result rows	Outside this module

Functions such as UPPER, LENGTH, ROUND, and NVL are single-row functions. If a query processes fifty rows, the expression can produce fifty function results. By contrast, an aggregate such as AVG summarizes a set or group. Analytic functions also consider multiple rows, but they retain row-level output and apply calculations through an analytic clause.

A function name alone is not enough to predict a reliable result. Apply the following four questions whenever you construct a function expression:

1. What datatype does the function expect? A character, number, datetime, timestamp, interval, LOB, or another value type may be required.
2. What datatype does it return? Formatting a number with TO_CHAR, for example, returns character data rather than another number.
3. Which null, character, collation, NLS, or time-zone rules apply? The same visible input can be interpreted differently when a query depends on implicit conversion or session settings.
4. Is the expression only presenting a value, or will another statement store it? A formatted report value is often unsuitable as the stored replacement for the original typed value.

Oracle can perform implicit datatype conversion when an argument does not have the expected type. Although convenient, implicit conversion can obscure intent and make a statement dependent on session configuration. Explicit conversion generally produces more readable, portable, and predictable SQL.

Real queries frequently combine functions. One function may trim incoming text, another may normalize its case, and a third may substitute a label when the result is null. Oracle evaluates the inner calls before passing their results outward. The order therefore forms part of the business rule. Trimming before measuring a value can produce a different length than measuring before trimming it.

Nesting also connects return datatypes. If an inner function returns character data, the outer function receives character data even when the original column was numeric or datetime. A formatted amount returned by TO_CHAR can be concatenated into a label, but arithmetic should normally be completed while the value is still numeric. Similarly, a formatted datetime string is useful for presentation but loses the direct datetime semantics needed for chronological arithmetic and comparison.

Readable expressions expose these boundaries. Format late, convert deliberately, and use aliases that describe the business meaning rather than the function name. If an expression becomes difficult to verify, calculate intermediate values in a common table expression or an inline view and give each step a meaningful alias. Compact SQL is not automatically clearer SQL.

A displayed value can look correct while having the wrong datatype for its next use. The text '125.50' and the number 125.50 may appear alike in a report, but they sort, compare, calculate, and convert according to different rules. Character sorting can place '100' before '20', whereas numeric sorting places 20 before 100.

Datatype also governs length, precision, collation, and available operations. Before exposing a calculated expression through a view, API, report, or export, verify both its displayed value and the datatype Oracle returns. This is particularly important for NVL, COALESCE, DECODE, and CASE, where multiple candidate expressions contribute to datatype resolution.

Oracle documents conversion rules and numeric precedence, but relying on an implicit conversion makes the dependency less visible. An explicit cast or conversion can communicate the contract to future readers and can turn an environment-dependent failure into a design decision that can be tested.

Character functions support common business requirements such as constructing display labels, normalizing case for presentation, trimming unwanted characters, locating a substring, and extracting a portion of text. This module groups them by purpose rather than reproducing Oracle's entire alphabetical function catalog.

• Case conversion: UPPER, LOWER, and INITCAP.
• Combination and padding: CONCAT, the || operator, LPAD, and RPAD.
• Trimming and replacement: LTRIM, RTRIM, TRIM, and REPLACE.
• Measurement and search: LENGTH and INSTR, together with variants for different units.
• Extraction: SUBSTR and its documented character, byte, and Unicode variants.
• Phonetic comparison: SOUNDEX, principally for approximate English-language matching.

The following self-contained query demonstrates three different operations and assigns an alias to each result:

SELECT UPPER('Oracle Database') AS upper_text,
       LENGTH('Oracle Database') AS character_count,
       INSTR('Oracle Database', 'Data') AS data_position
FROM   dual;

UPPER returns a case-converted character value. LENGTH returns the number of characters under its documented semantics, and INSTR returns the position where the requested substring begins. The source literal is unchanged.

Unit selection matters with multilingual data. LENGTH counts characters, whereas LENGTHB counts bytes. Those values can differ when the database character set represents a character with more than one byte. The same distinction applies to functions such as INSTRB and SUBSTRB. Choose a byte variant only when the requirement is genuinely defined in bytes.

ASCIISTR is sometimes misclassified in older function lists. It returns an ASCII representation of character data, using escape notation for non-ASCII characters; it is not a character function that returns a number.

Oracle provides numeric functions for absolute values, ceilings, floors, remainders, powers, signs, square roots, rounding, and truncation. The correct function depends on the business rule. A reporting requirement that rounds currency is different from a rule that must discard digits without rounding.

SELECT ROUND(1234.567, 2) AS rounded_value,
       TRUNC(1234.567, 2) AS truncated_value
FROM   dual;

With a second argument of 2, ROUND returns 1234.57, while TRUNC returns 1234.56. The distinction can materially affect financial and scientific results. A production calculation should therefore encode the intended rule rather than choose a function solely because the displayed outputs often appear similar.

Oracle also documents datetime forms of ROUND and TRUNC. Their arguments and return behavior differ from the numeric forms. Lesson 6 develops those overloads together with number and datetime formatting.

An Oracle DATE value is not stored as the formatted text shown by a client. Formatting is a presentation operation. When an application depends on the session's default date format, the same statement can display differently or fail after an NLS setting changes. Reusable SQL should use explicit datetime literals and format models.

SELECT TO_CHAR(DATE '2026-08-23', 'YYYY-MM-DD') AS formatted_date
FROM   dual;

The ANSI date literal identifies the value without depending on NLS_DATE_FORMAT. TO_CHAR then deliberately returns the character text 2026-08-23. The format model controls the output; it does not change the internal date value. New examples should avoid two-digit years and ambiguous strings such as '08/23/26'.

“Current time” also requires precision. SYSDATE returns a DATE based on the database host, whereas SYSTIMESTAMP includes fractional seconds and time-zone information from that system. CURRENT_DATE and CURRENT_TIMESTAMP reflect the session time zone and return different datetime types. Lesson 8 examines these distinctions in detail.

Null represents an unknown or inapplicable value. It is not numeric zero, a space, or a missing row. Most scalar functions return null when given a null argument, although each function's documentation controls its exact behavior. Arithmetic involving null also produces null, so queries must handle null deliberately when a business result requires a substitute.

NVL(expr1, expr2) returns expr2 when expr1 is null and otherwise returns expr1. The datatypes of both arguments matter because Oracle may attempt an implicit conversion. An incompatible substitute can raise a conversion error even when its text seems reasonable to a reader.

COALESCE is a SQL-standard alternative that returns the first non-null expression from a list and uses short-circuit evaluation. It is especially useful when several possible values must be considered. This module retains NVL because it is widely used in Oracle SQL, while also teaching students to recognize the more general alternative.

DECODE compares an expression with successive search values and returns the result associated with the first match. It remains supported in Oracle AI Database 26ai. A CASE expression is normally clearer for new SQL, particularly when conditions involve ranges or predicates rather than equality comparisons. Lesson 9 compares these choices and their datatype behavior.

Functions often cross datatype boundaries. TO_CHAR creates presentation text from a number or datetime value. TO_DATE, TO_TIMESTAMP, and TO_NUMBER interpret character data according to their arguments and format models. Explicit conversion makes the intended boundary visible and reduces accidental dependence on NLS settings.

Character comparison and transformation can also be collation-sensitive. Case conversion, linguistic sorting, and substring operations should be designed with the application's language and character-set requirements in mind. A result that works for basic ASCII test data does not establish that byte offsets, capitalization, or phonetic matching will work correctly for every language.

Function placement can affect access paths. Applying a function directly to a filtered column may prevent Oracle from using an ordinary index on the unmodified column. Depending on the workload, a matching function-based index or another design may be appropriate. This module concentrates on correct expressions; indexing and optimizer decisions belong to later performance work.

A function example should be tested with more than the expected value. Character tests should include null, empty, padded, mixed-case, and multilingual input where those values are permitted. Numeric tests should include positive and negative values, zero, boundary precision, and digits on both sides of the requested rounding position. Datetime tests should cross month, year, daylight-saving, and time-zone boundaries when the application depends on those conditions.

Session state belongs in the test plan. Record relevant NLS, collation, and time-zone settings when a result depends on them. Better still, use explicit literals, conversions, and format models when the business rule should remain stable across sessions. Testing only the text shown by one SQL client can hide a datatype or environment dependency that another client later exposes.

Finally, compare the expression with the requirement. Rounding for display is not necessarily the same as rounding before storage or aggregation. Replacing null for presentation is not the same as declaring that the replacement is the actual business value. SQL functions implement decisions; they do not decide which interpretation is correct for the organization.

The remaining lessons move from character transformations to numeric, datetime, null, and conditional operations:

1. Character string functions: organize functions by purpose and return type.
2. CONCAT, LENGTH, and INSTR: combine, measure, and search character values.
3. SUBSTR: extract text using positive or negative positions and an optional length.
4. UPPER, INITCAP, RTRIM, and SOUNDEX: transform and compare character output.
5. TO_CHAR, ROUND, and TRUNC: format values and apply precise numeric or datetime rules.
6. Oracle datetime representation and formatting: separate typed values from NLS-dependent display text.
7. Current datetime functions: select the correct database-host or session-sensitive value.
8. DECODE and NVL: implement conditional mappings and null substitution while comparing modern alternatives.

Throughout the module, keep the same four decisions in view: expected input, returned datatype, null and environment behavior, and whether the result is being presented or persisted. Those questions make function composition predictable and help prevent errors that remain hidden in small test cases.

The next lesson begins with Oracle character string functions and establishes the return-type and character-semantics distinctions needed for the subsequent lessons.