Oracle SQL Certification 1Z0-071 Course | Section 7: Customizing Output with Single-Row Functions

Single-row functions are heavily used in real-world SQL queries and appear frequently in certification exam questions. Mastering them will significantly improve your ability to customize query output and solve complex data manipulation problems.

This section of the Oracle SQL 1Z0-071 course focuses on single-row functions, also called scalar functions. These functions are essential for transforming, formatting, and analyzing data at the row level. They operate on one row at a time and return exactly one value for each row processed.

What Are Single-Row Functions

Single-row functions:

  • Operate on individual rows
  • Accept zero, one, or multiple parameters
  • Always return a single value per row
  • Can be used in SELECT, WHERE, ORDER BY, and other SQL clauses

Oracle provides a rich set of built-in single-row functions, which can be grouped into several categories:

  • Character functions
  • Numeric functions
  • Date and time functions
  • Analytical functions

This section explores each category in detail with exam-relevant behavior and practical examples.

Character Functions

Character functions allow you to manipulate and format text data. They are commonly used for cleaning input, formatting output, and performing case-insensitive comparisons.

UPPER and LOWER

The UPPER and LOWER functions convert text to uppercase or lowercase.

Common use cases include:

  • Standardizing text output
  • Performing case-insensitive comparisons in the WHERE clause
  • Nesting functions for flexible formatting

A common exam pattern is using UPPER or LOWER on both sides of a comparison to avoid case sensitivity issues.

INITCAP

INITCAP formats text by converting the first letter of each word to uppercase and the remaining letters to lowercase.

Key behaviors to remember:

  • Words are identified by spaces and special characters
  • Numbers are not modified
  • Implicit conversion may occur if numeric data is passed

INITCAP is frequently used to produce clean, readable output in reports.

String Concatenation

Oracle supports multiple ways to concatenate strings:

  • CONCAT function (limited to two parameters)
  • Double pipe operator (||), which is more flexible and commonly used

Important notes:

  • Numbers are implicitly converted to text when concatenated
  • Literals, spaces, and column values can be combined freely

String concatenation is a fundamental skill for building readable result sets.

Padding Functions LPAD and RPAD

LPAD and RPAD add characters to the left or right side of a string.

Key points:

  • Padding can use spaces or custom characters
  • Two-parameter and three-parameter forms are supported
  • Numbers and dates are implicitly converted to strings

These functions are often used to align output in reports.

LENGTH

The LENGTH function returns the number of characters in a string.

Important considerations:

  • Counts characters, not bytes
  • Implicit conversion applies to numeric input

LENGTH is commonly used for validation and conditional logic.

INSTR

INSTR searches for a substring within a string and returns its position.

Features include:

  • Optional start position
  • Optional occurrence parameter
  • Returns zero if the substring is not found

INSTR is frequently combined with SUBSTR in exam and real-world scenarios.

Trimming Functions

Oracle provides several trimming functions:

  • RTRIM removes characters from the right
  • LTRIM removes characters from the left
  • TRIM removes characters from both sides

TRIM supports LEADING, TRAILING, and BOTH options.

Key exam rule:

  • Use IS NULL, not equality operators, when handling NULL values after trimming
SUBSTR

SUBSTR extracts a portion of a string starting at a specific position.

Important behavior:

  • Length parameter is optional
  • Works with positive and negative positions

SUBSTR is a core function for parsing structured text values.

SOUNDEX

SOUNDEX allows comparison of strings based on pronunciation rather than spelling.

Common use cases:

  • Matching names with spelling variations
  • Flexible search conditions in WHERE clauses

This function appears occasionally in exam questions and is useful for real-world data matching.

Numeric Functions

Numeric functions are used for calculations, rounding, and numeric transformations.

Functions covered include:

  • ABS
  • POWER and SQRT
  • ROUND and TRUNC
  • CEIL and FLOOR
  • MOD and REMAINDER

Key exam focus:

  • Differences between rounding and truncation
  • Behavior of negative numbers
  • Differences between MOD and REMAINDER

Numeric functions are often combined with arithmetic expressions in SELECT and WHERE clauses.

Date and Time Functions

Oracle date functions allow you to work with dates, times, and intervals.

Important topics include:

  • SYSDATE for current date and time
  • Adding and subtracting days and months
  • ROUND and TRUNC with dates
  • MONTHS_BETWEEN
  • NEXT_DAY and LAST_DAY
  • INTERVAL YEAR TO MONTH
  • INTERVAL DAY TO SECOND

Key exam reminder:

  • DATE data type always includes time
  • Many date operations rely on implicit numeric behavior
Analytical Functions

Analytical functions perform calculations across a set of rows while still returning one row per result.

OVER and PARTITION BY

Using aggregate functions with OVER allows:

  • Running totals
  • Window-based calculations
  • Partitioned analysis by groups

PARTITION BY splits the result set into independent calculation groups.

LAG and LEAD

LAG and LEAD access values from previous or following rows.

Key points:

  • Require ORDER BY in the OVER clause
  • Support optional offset and default values
  • Return NULL at dataset boundaries

These functions are commonly used for trend analysis and comparisons.

Additional Analytical Functions

Oracle also supports analytical versions of:

  • MIN
  • MAX
  • AVG
  • MEDIAN
  • STDDEV
  • VARIANCE

These functions scan the full dataset or partition and return row-level analytical results.

Nesting Functions

Nested functions allow the output of one function to be passed as input to another.

Important concepts:

  • Inner functions execute first
  • Outer functions operate on the returned value
  • Common patterns include parsing strings with SUBSTR and INSTR

Nesting functions is a critical skill for both exam success and real-world SQL development.

Exam Tips for Single-Row Functions
  • Single-row functions return one value per row
  • They can be used in SELECT, WHERE, and ORDER BY
  • Implicit data type conversion is common and often tested
  • Nested functions appear frequently in exam questions
  • Analytical functions require OVER and often ORDER BY

This section completes your foundation in customizing output using single-row functions. Practice combining these functions in realistic queries to gain confidence and prepare effectively for the Oracle SQL 1Z0-071 certification exam.

Now already involved in this SQL journey, you can subscribe to the YouTube channel and maybe subscribe to newsletter below?

Join ForgeFuse Newsletter