6 Single-Row Built-In Functions

6.1 Module Introduction

In this module, students will explore single-row functions in Oracle SQL, focusing on character, number, conversion, and null-handling functions, as well as conditional logic and introductory regular expressions. These functions are foundational for data transformation and conditional logic in SELECT queries.

Reference: Oracle SQL by Example (4th Edition), Chapter 4, pp. 133–187

6.2 Explanation

6.2.1 Character Functions

Character functions allow manipulation and evaluation of string values.

Examples:

SELECT student_id, 
       UPPER(first_name) AS upper_name, 
       LENGTH(last_name) AS name_length
FROM student;

This query demonstrates converting names to uppercase and calculating the length of last names.

Common functions: UPPER, LOWER, INITCAP, SUBSTR, INSTR, TRIM, LPAD, RPAD, LENGTH

Refer to Table 4.2, Which Character Function Should You Use? in Lab 4.1 for a full list and description of character functions.

6.2.2 Number Functions

Number functions perform arithmetic or rounding operations.

Example:

SELECT course_no, 
       ROUND(cost, -2) AS rounded_cost
FROM course;

This query rounds course costs to the nearest hundred dollars.

Common functions: ROUND, TRUNC, MOD, FLOOR, CEIL, ABS, SIGN, POWER, SQRT, EXP, LOG

Refer to Table 4.3, Which Number Function Should You Use? in Lab 4.2 for a full list and description of number functions.

6.2.3 Conversion Functions

Used to convert data types between strings, numbers, and dates.

Example:

SELECT TO_CHAR(registration_date, 'YYYY-MM-DD') AS reg_date_str
FROM student;

This query converts registration dates to a standardized string format.

Common functions: TO_CHAR, TO_DATE, CAST, TO_NUMBER

6.2.4 Oracle’s Automatic Data Conversion

Oracle automatically converts between data types when comparing different types in WHERE clauses.

Example:

The ZIP column in the ZIPCODE table is stored as VARCHAR2(5), but you can search using numeric values:

-- This works even though ZIP is stored as text
SELECT zip, city
FROM zipcode
WHERE zip = 10025;

Oracle automatically converts the numeric literal 10025 to '10025' to match the VARCHAR2 column.

Performance Note: Avoid applying conversion functions to columns in WHERE clauses as this can disable index usage:

-- Avoid: can disable indexes
SELECT zip, city
FROM zipcode
WHERE TO_NUMBER(zip) = 10025

-- Prefer: maintains performance
SELECT zip, city
FROM zipcode
WHERE zip = TO_CHAR(10025)

Readability Note: While automatic conversion is convenient, explicit conversion functions like TO_CHAR() and TO_NUMBER() can make queries clearer and more predictable.

6.2.5 Null Handling

These functions help manage NULL values effectively.

Example:

SELECT NVL(phone,'No phone') AS contact_number
  FROM student
 ORDER BY student_id;

This query replaces NULL phone numbers with a default message.

Common functions: NVL, NVL2, COALESCE, NULLIF, LNNVL

6.2.6 Conditional Logic

SQL offers conditional functions like CASE and DECODE for inline logic.

Example:

SELECT student_id,
       zip,
       CASE WHEN zip <= '10025' THEN 'Downtown'
            WHEN zip >= '11216' THEN 'Northside'
            ELSE 'Other'
       END AS region
FROM student;

This query assigns region names based on ZIP codes using CASE logic.

Alternative using DECODE:

SELECT DECODE(zip, '30303', 'Downtown', '30342', 'Northside', 'Other') AS region
FROM student;

This query achieves the same result using Oracle’s DECODE function.

Refer to Table 4.4, Which Functions and CASE Expressions Should You Use? in Lab 4.3 for a full list and description of conditional functions.

6.2.7 String Manipulation Functions

This category includes concatenation, replacement, and regex-based transformations.

Concatenation:

SELECT student_id, 
       first_name || ' ' || last_name AS full_name
FROM student;

This query combines first and last names with a space separator.

REPLACE:

SELECT student_id, 
       REPLACE(phone, '-', '') AS phone_cleaned
FROM student;

This query removes dashes from phone numbers.

REGEXP_REPLACE:

SELECT student_id, 
       REGEXP_REPLACE(phone, '[^0-9]', '') AS phone_digits_only
FROM student;

This query extracts only numeric digits from phone numbers using regular expressions.

Other regex functions: REGEXP_LIKE, REGEXP_INSTR, REGEXP_SUBSTR

Refer to Lab 16.1, Regular Expressions for a detailed guide to using regular expressions.

6.2.8 Regular Expressions

Regular expressions provide powerful pattern matching capabilities for complex string operations.

SELECT zip
FROM zipcode
WHERE REGEXP_LIKE(zip, '[^0-9]');

This query finds ZIP codes that contain non-numeric characters.

Regular expressions can get very complicated, but they are essentially a form a pattern matching in text. See Stanford’s CS103 Notes for a high-level overview.

6.3 Exercises

Use the following prompts to reinforce your understanding of single-row functions. These exercises reference the STUDENT, COURSE, and ZIPCODE tables.

Search for student last names beginning with ‘Mo’ Use the STUDENT table.
Find student names containing a period and order results by last name length Use the STUDENT table.
Format the CITY, STATE, and ZIP columns into a single readable address line separated by commas Use the ZIPCODE table.
Display the course costs increased by 75% and round to the nearest dollar Use the COST column in the COURSE table.
Custom output for prerequisite courses based on conditional logic Write a query that selects the course number, description, and prerequisite from the COURSE table for the following course numbers: 20, 120, 122, and 132. Alias the prerequisite column as “Original”. Then, use a CASE statement to create a new column with customized output: if the prerequisite is course number 120, substitute it with ‘200’; if the prerequisite is 130, substitute with ‘N/A’; if the prerequisite is null, display ‘None’. For all other prerequisites, display the original value as a character. Sort the results by course number in descending order.
Find zip codes that contain non-digit characters Use a regular expression function to identify all rows in the ZIPCODE table where the ZIP column contains any characters that are not numeric digits.

6.4 Q&A

Open floor for questions and best practices discussion. Consider these topics:

When to use character functions vs. regular expressions for pattern matching
Best practices for handling NULL values in reporting queries
Performance considerations when using functions in WHERE clauses
Common pitfalls when converting between data types

6.5 Additional Resources

Oracle Documentation on SQL Functions
Practice more with the STUDENT schema using Labs 4.1–4.4 in the textbook

6.6 Answers

Search for student last names beginning with ‘Mo’

SELECT first_name, last_name
FROM student
WHERE SUBSTR(last_name, 1, 2) = 'Mo';

Find student names containing a period and order results by last name length

SELECT first_name, last_name
FROM student
WHERE INSTR(first_name, '.') > 0
ORDER BY LENGTH(last_name);

Format the city, state, and zip columns into a single readable address line separated by commas

SELECT city || ', ' || state || ', ' || zip AS formatted_address
FROM zipcode;

Display the course costs increased by 75% and round to the nearest dollar

SELECT cost, 
       cost * 1.75 AS increased,
       ROUND(cost * 1.75) AS rounded_increased
FROM course;

Custom output for prerequisite courses based on conditional logic

SELECT course_no, 
       description, 
       prerequisite AS "Original",
       CASE WHEN prerequisite = 120 THEN '200'
            WHEN prerequisite = 130 THEN 'N/A'
            WHEN prerequisite IS NULL THEN 'None'
            ELSE TO_CHAR(prerequisite)
       END AS "NEW"
FROM course
WHERE course_no IN (20, 120, 122, 132)
ORDER BY course_no DESC;

Find zip codes that contain non-digit characters

SELECT zip
FROM zipcode
WHERE REGEXP_LIKE(zip, '[^0-9]');