CPAN 121

LECTURE 5 – NUMBER FUNCTIONS AND OTHERS

Tuesday, March 24, 2026 CPAN 121 SQL 1

Objectives – NUMBER Functions
•This part of the lesson covers the following objectives:
◦ Select and apply the single-row number functions ROUND, TRUNC, and MOD in a SQL
query
◦ Distinguish between the results obtained when TRUNC is applied to a numeric value
and ROUND is applied to a numeric value
◦ State the implications for business when applying TRUNC and ROUND to numeric
values

Tuesday, March 24, 2026 CPAN 121 SQL 2

Purpose – Number Functions
•One of the reasons we put our money in a bank is to take advantage of the
interest it accumulates over time
•Banks adjust the interest rate with various economic indicators such as inflation
and the stock market
•Typically, interest rates are expressed as a percent such as 3.45%

Tuesday, March 24, 2026 CPAN 121 SQL 3

Purpose – Number Functions
•If a bank decided to round the percentage rate to 3.5%, would it be to your
advantage?
•If it decided to just drop the decimal values and calculate the interest at 3%,
would you be happy then?
•Rounding and truncating numbers play an important part in business and in turn
with the databases that support these businesses as they store and access
numeric data

Tuesday, March 24, 2026 CPAN 121 SQL 4

Number Functions
•The three number functions are:
◦ ROUND
◦ TRUNC
◦ MOD

Tuesday, March 24, 2026 CPAN 121 SQL 5

ROUND Function
The ROUND function is designed to round numeric values to the given precision
The syntax of the ROUND function is:
◦ ROUND(n, p)
◦ Where n represents the numeric data or column to be rounded and p
represents the position of the digit to which the data should be rounded
◦ If p is a positive number then round on the right side of the decimal position
◦ If p is a negative number then round on the left side of the decimal position

Tuesday, March 24, 2026 CPAN 121 SQL 6

ROUND Function
•ROUND can be used with both numbers and dates
•It is mainly used to round numbers to a specified number of decimal places, but
it can also be used to round numbers to the left of the decimal point
•Syntax:
ROUND(column|expression, decimal places)
•Note that if the number of decimal places is not specified or is zero, the number
will round to no decimal places
•ROUND(45.926) 46
•ROUND(45.926, 0)46

Tuesday, March 24, 2026 CPAN 121 SQL 7

ROUND Function
•If the number of decimal places is a positive number, the number is rounded to
that number of decimal places to the right of the decimal point
•ROUND(45.926, 2) 45.93
If the number of decimal places is a negative number, the number is rounded to
that number of decimal places to the left of the decimal point
•ROUND(45.926, -1) 50

Tuesday, March 24, 2026 CPAN 121 SQL 8

ROUND Function
•Most are rounded
to the nearest
dollar. They round
up to an even dollar
because of the 95 in
the original data.
•Look at The Wok
Way to Cook. It is
rounded up to 8
from .75

Tuesday, March 24, 2026 CPAN 121 SQL 9

ROUND Function
•A value of 0 for p
indicates that the
number should be
rounded to the nearest
whole number with no
decimal places

Tuesday, March 24, 2026 CPAN 121 SQL 10

ROUND Function
•The –1 indicates
rounding on the left side
of the decimal. This
would round to the
nearest tens of dollars

Tuesday, March 24, 2026 CPAN 121 SQL 11

TRUNC Function
•The TRUNC function can be used with both numbers and dates. It is mainly used
to terminate the column, expression, or value to a specified number of decimal
places.
•When TRUNC is used, if the number of decimal places is not specified, then like
ROUND, the specified number defaults to zero.
•Syntax: TRUNC(column|expression, decimal places)

Tuesday, March 24, 2026 CPAN 121 SQL 12

TRUNC Function
•The TRUNC function is used to truncate numeric data rather than round it
•The TRUNC function can be used to truncate numeric data to a specific position.
Any numbers after that position are simply removed or dropped off
•The syntax for the TRUNC function is:
◦ TRUNC(n, p)
◦ Where n represents the numeric data or field to be truncated and p
represents the position from the digit from which the data should be
removed
◦ Whether p is positive or negative, it follows the same rules as it did for the
ROUND function

Tuesday, March 24, 2026 CPAN 121 SQL 13

TRUNC Function
•TRUNC function used with
a p value of 1, 0 and –1.
With –1, the digit to the left
of the decimal reverts to a
zero, it is still needed as a
placeholder

Tuesday, March 24, 2026 CPAN 121 SQL 14

MOD Function
•The MOD function finds the remainder after one value is divided by another
value.
•For example, the MOD of 5 divided by 2 is 1.
•MOD can be used to determine whether a value is odd or even. If you divide a
value by 2 and there is no remainder, the number must be an even number.
•For example, if the MOD of x divided by 2 is 0, then x must be an even number.

SELECT country_name, MOD(airports,2)

AS "Mod Demo"
FROM wf_countries;

Tuesday, March 24, 2026 CPAN 121 SQL 15

MOD Function
•Can use the DUAL table to
show the result as well

•There are 16 oz. in a pound,

this is the older Imperial
measure

Tuesday, March 24, 2026 CPAN 121 SQL 16

MOD Function

Tuesday, March 24, 2026 CPAN 121 SQL 17

Terminology for This Portion
•Key terms used in this lesson included: Number functions
◦ MOD
◦ ROUND
◦ TRUNC

Tuesday, March 24, 2026 CPAN 121 SQL 18

Summary for This Portion
•In this lesson, you should have learned how to:
• Select and apply the single-row number functions ROUND, TRUNC, and MOD in a SQL
query
• Distinguish between the results obtained when TRUNC is applied to a numeric value
and ROUND is applied to a numeric value
• State the implications for business when applying TRUNC and ROUND to numeric
values

Tuesday, March 24, 2026 CPAN 121 SQL 19

Objectives for Dates
•This portion of the lesson covers the following objectives:
• Demonstrate the use of SYSDATE and date functions
• State the implications for world businesses to be able to easily manipulate data
stored in date format

Tuesday, March 24, 2026 CPAN 121 SQL 20

Purpose for Dates
•Have you ever wondered how many days remain in the school year or how
many weeks there are until graduation?
•Because the Oracle database stores dates as numbers, you can easily perform
calculations on dates using addition, subtraction, and other mathematical
operators
•Businesses depend on being able to use date functions to schedule payrolls and
payments, track employee performance reviews and years of service, or keep
track of orders and shipments
•All of these business needs are easily handled using simple SQL date functions

Tuesday, March 24, 2026 CPAN 121 SQL 21

Displaying Dates
•The default display and input format for dates is: DD-Mon-YYYY
•For example: 02-Dec-2014
•However, the Oracle database stores dates internally with a numeric format
representing the century, year, month, day, hour, minute, and second
•Valid Oracle dates are between January 1, 4712 B.C., and December 31, 9999
A.D.
•This represents the range of dates that you can store successfully in an Oracle
database

Tuesday, March 24, 2026 CPAN 121 SQL 22

SYSDATE
•SYSDATE is a date function that returns the current database server date and
time.
•Use SYSDATE to display the current date, use the DUAL table.

SELECT SYSDATE FROM

dual;

Tuesday, March 24, 2026 CPAN 121 SQL 23

DATE Data Type
•The DATE data type always stores year information as a four-digit number
internally: two digits for the century and two digits for the year.
•For example, the Oracle database stores the year as 1996 or 2004, not just as 96
or 04.
•In previous versions, the century component was not displayed by default.
•However, due to changing business requirements around the world, the 4-digit
year is now the default display.

Tuesday, March 24, 2026 CPAN 121 SQL 24

Working with Dates

Tuesday, March 24, 2026 CPAN 121 SQL 25

DATE Functions
•The date functions shown in the table operate on Oracle dates.
•All the date functions return a value with a DATE data type except the
MONTHS_BETWEEN function, which returns a numeric data type value.

Tuesday, March 24, 2026 CPAN 121 SQL 26

DATE Functions
•MONTHS_BETWEEN: takes 2 DATE arguments and returns the number of
calendar months between the 2 dates
•If the first argument is an earlier date than the second, the number returned is
negative

Tuesday, March 24, 2026 CPAN 121 SQL 27

DATE Functions
•ADD_MONTHS: takes 2 arguments, a DATE and a number
•Returns a DATE value with the number argument added to the month
component of the date
•If the number supplied is negative, the function will subtract that number of
months from the date argument

Tuesday, March 24, 2026 CPAN 121 SQL 28

DATE Functions
•NEXT_DAY: takes 2 arguments, a DATE and a weekday and returns the DATE of
the next occurrence of that weekday after the DATE argument

Tuesday, March 24, 2026 CPAN 121 SQL 29

DATE Functions
•LAST_DAY: takes a DATE argument and returns the DATE of the last day of the
month for the DATE argument

Tuesday, March 24, 2026 CPAN 121 SQL 30

DATE Functions
•ROUND: returns a DATE rounded to the unit specified by the second argument

Tuesday, March 24, 2026 CPAN 121 SQL 31

DATE Functions
•TRUNC: returns a DATE truncated to the unit specified by the second argument

Tuesday, March 24, 2026 CPAN 121 SQL 32

DATE Functions
•Here is an example of a query using multiple date functions
•The output is displayed on the next slide

Tuesday, March 24, 2026 CPAN 121 SQL 33

DATE Functions
•The result set from this query returns 20 rows including:

Tuesday, March 24, 2026 CPAN 121 SQL 34

Terminology this Portion
•Key terms used in this lesson included:
◦ ADD_MONTHS
◦ LAST_DAY
◦ MONTHS_BETWEEN
◦ NEXT_DAY
◦ SYSDATE
◦ ROUND
◦ TRUNC

Tuesday, March 24, 2026 CPAN 121 SQL 35

Summary this Portion
•In this lesson, you should have learned how to:
◦ Select and apply the single-row functions MONTHS_BETWEEN, ADD_MONTHS,
NEXT_DAY, LAST_DAY, ROUND, and TRUNC that operate on date data
◦ Explain how date functions transform Oracle dates into date data or numeric values
◦ Demonstrate proper use of the arithmetic operators with dates

Tuesday, March 24, 2026 CPAN 121 SQL 36

Summary this Portion
•In this lesson, you should have learned how to:
• Demonstrate the use of SYSDATE and date functions
• State the implications for world businesses to be able to easily manipulate data
stored in date format

Tuesday, March 24, 2026 CPAN 121 SQL 37

Objectives – DATE Functions
•This lesson covers the following objectives:
◦ Demonstrate the use of SYSDATE and date functions
◦ State the implications for world businesses to be able to easily manipulate data
stored in date format

Tuesday, March 24, 2026 CPAN 121 SQL 38

Purpose – DATE Functions
•Have you ever wondered how many days remain in the school year or how
many weeks there are until graduation?
•Because the Oracle database stores dates as numbers, you can easily perform
calculations on dates using addition, subtraction, and other mathematical
operators
•Businesses depend on being able to use date functions to schedule payrolls and
payments, track employee performance reviews and years of service, or keep
track of orders and shipments
•All of these business needs are easily handled using simple SQL date functions

Tuesday, March 24, 2026 CPAN 121 SQL 39

Displaying Dates
•The default display and input format for dates is:
◦ DD-Mon-YYYY
•For example: 02-Dec-2014
•However, the Oracle database stores dates internally with a numeric format
representing the century, year, month, day, hour, minute, and second
•Valid Oracle dates are between January 1, 4712 B.C., and December 31, 9999
A.D.
•This represents the range of dates that you can store successfully in an Oracle
database

Tuesday, March 24, 2026 CPAN 121 SQL 40

SYSDATE
•SYSDATE is a date function that returns the current database server date and
time
•Use SYSDATE to display the current date, use the DUAL table

SELECT SYSDATE FROM dual;

Tuesday, March 24, 2026 CPAN 121 SQL 41

DATE Data Type
•The DATE data type always stores year information as a four-digit number
internally: two digits for the century and two digits for the year
•For example, the Oracle database stores the year as 1996 or 2004, not just as 96
or 04
•In previous versions, the century component was not displayed by default
•However, due to changing business requirements around the world, the 4-digit
year is now the default display

Tuesday, March 24, 2026 CPAN 121 SQL 42

Working with Dates

Tuesday, March 24, 2026 CPAN 121 SQL 43

Date Functions
•The date functions shown in the table operate on Oracle dates
•All the date functions return a value with a DATE data type except the
MONTHS_BETWEEN function, which returns a numeric data type value

Tuesday, March 24, 2026 CPAN 121 SQL 44

Date Functions
•MONTHS_BETWEEN: takes 2 DATE arguments and returns the number of
calendar months between the 2 dates
•If the first argument is an earlier date than the second, the number returned is
negative

Tuesday, March 24, 2026 CPAN 121 SQL 45

Date Functions
•ADD_MONTHS: takes 2 arguments, a DATE and a number
•Returns a DATE value with the number argument added to the month
component of the date
•If the number supplied is negative, the function will subtract that number of
months from the date argument

Tuesday, March 24, 2026 CPAN 121 SQL 46

Date Functions
•NEXT_DAY: takes 2 arguments, a DATE and a weekday and returns the DATE of
the next occurrence of that weekday after the DATE argument

Tuesday, March 24, 2026 CPAN 121 SQL 47

Date Functions
•LAST_DAY: takes a DATE argument and returns the DATE of the last day of the
month for the DATE argument

Tuesday, March 24, 2026 CPAN 121 SQL 48

Date Functions
•ROUND: returns a DATE rounded to the unit specified by the second argument

Tuesday, March 24, 2026 CPAN 121 SQL 49

Date Functions
•TRUNC: returns a DATE truncated to the unit specified by the second argument

Tuesday, March 24, 2026 CPAN 121 SQL 50

Date Functions
•Here is an example of a query using multiple date functions
•The output is displayed on the next slide

Tuesday, March 24, 2026 CPAN 121 SQL 51

Date Functions
•The result set from this query returns 20 rows including:

Tuesday, March 24, 2026 CPAN 121 SQL 52

Terminology – This Portion
•Key terms used in this lesson included:
◦ ADD_MONTHS
◦ LAST_DAY
◦ MONTHS_BETWEEN
◦ NEXT_DAY
◦ SYSDATE
◦ ROUND
◦ TRUNC

Tuesday, March 24, 2026 CPAN 121 SQL 53

Summary – This Portion
•In this lesson, you should have learned how to:
◦ Select and apply the single-row functions MONTHS_BETWEEN, ADD_MONTHS,
NEXT_DAY, LAST_DAY, ROUND, and TRUNC that operate on date data
◦ Explain how date functions transform Oracle dates into date data or numeric values
◦ Demonstrate proper use of the arithmetic operators with dates

Tuesday, March 24, 2026 CPAN 121 SQL 54

Summary – This Portion
•In this lesson, you should have learned how to:
• Demonstrate the use of SYSDATE and date functions
• State the implications for world businesses to be able to easily manipulate data
stored in date format

Tuesday, March 24, 2026 CPAN 121 SQL 55

Objectives – Conversion Functions
•This lesson covers the following objectives:
◦ Provide an example of an explicit data-type conversion and an implicit data-type
conversion
◦ Explain why it is important, from a business perspective, for a language to have built-
in data-conversion capabilities
◦ Construct a SQL query that correctly applies TO_CHAR, TO_NUMBER, and TO_DATE
single-row functions to produce a desired result

Tuesday, March 24, 2026 CPAN 121 SQL 56

Objectives – Conversion Functions
•This lesson covers the following objectives:
◦ Apply the appropriate date and/or character format model to produce a desired
output
◦ Explain and apply the use of YY and RR to return the correct year as stored in the
database

Tuesday, March 24, 2026 CPAN 121 SQL 57

Purpose – Conversion Functions
•Imagine having to read all your schoolbooks in text files with no paragraphs and
no capitalization
•It would be difficult to read
•Fortunately, there are software programs available to capitalize and color text,
underline, bold, center, and add graphics
•For databases, format and display changes are done using conversion functions
•These functions are able to display numbers as local currency, format dates in a
variety of formats, display time to the second, and keep track of what century a
date refers to

Tuesday, March 24, 2026 CPAN 121 SQL 58

Data Types
•When a table is created for a database, the SQL programmer must define what
kind of data will be stored in each field of the table
•In SQL, there are several different data types. These data types define the
domain of values that each column can contain
•For this lesson, you will use:VARCHAR2
◦ CHAR
◦ NUMBER
◦ DATE

Tuesday, March 24, 2026 CPAN 121 SQL 59

Data Types Described
•VARCHAR2: Used for character data of variable length, including numbers,
dashes, and special characters
•CHAR: Used for text and character data of fixed length, including numbers,
dashes, and special characters
•NUMBER: Used to store variable-length numeric data. No dashes, text, or other
nonnumeric data are allowed Currency is stored as a number data type
•DATE: Used for date and time values. Internally, Oracle stores dates as numbers
and, by default, DATE information is displayed as DD-Mon-YYYY (for example, 23-
Oct-2013)

Tuesday, March 24, 2026 CPAN 121 SQL 60

Type Conversion
•The Oracle Server can automatically convert VARCHAR2 and CHAR data to
NUMBER and DATE data types
•It can convert NUMBER and DATE data back to CHARACTER data type
•This is known as implicit data conversion

Implicit Data
Type
Data Type
Conversions

Explicit Data
Type

Tuesday, March 24, 2026 CPAN 121 SQL 61

Type Conversion
•Although this is a convenient feature, it is always best to explicitly make data
type conversions to ensure reliability in SQL statements

Tuesday, March 24, 2026 CPAN 121 SQL 62

Type Conversion
•The four data type conversion functions you will learn are:
◦ To convert date data type to character data type
◦ To convert number data type to character data type

Tuesday, March 24, 2026 CPAN 121 SQL 63

Type Conversion
•The four data-type conversion functions you will learn are:
◦ To convert character data type to number data type
◦ To convert character data type to date data types

Tuesday, March 24, 2026 CPAN 121 SQL 64

Date Conversion to Character Data
•It is often desirable to convert a date from its default DD-Mon-YYYY format to
another format specified by you
•The function to accomplish this task is:
TO_CHAR (date column name, 'format model you specify')

•The 'format model' must be enclosed in single quotation marks and is case-
sensitive
•Separate the date value from the format model with a comma
•Any valid date format element can be included

Tuesday, March 24, 2026 CPAN 121 SQL 65

Date Conversion to Character Data
•Use sp to spell out a number
•Use th to have the number appear as an ordinal (1st, 2nd, 3rd, and so on)
•Use an fm element to remove padded blanks or remove leading zeroes from the
output

Tuesday, March 24, 2026 CPAN 121 SQL 66

Date Conversion to Character Data
•The tables show the
different format models that
can be used
•When specifying time
elements, note that hours
(HH), minutes (MI), seconds
(SS), and AM or PM can also
be formatted

Tuesday, March 24, 2026 CPAN 121 SQL 67

Date Conversion to Character Data
Examples of output using different format models:

Tuesday, March 24, 2026 CPAN 121 SQL 68

Date Conversion to Character Data
•Examples of output using different format models for time:

Tuesday, March 24, 2026 CPAN 121 SQL 69

Number Conversion to Character
Data (VARCHAR2)
•Numbers stored in the database have no formatting
•This means that they have no currency signs/symbols, commas, decimals, or
other formatting
•To add formatting, you first need to convert the number to a character format

TO_CHAR(number, 'format model')

•
•The SQL function that you use to convert a number to a desired character
format is:

Tuesday, March 24, 2026 CPAN 121 SQL 70

Number Conversion to Character
Data (VARCHAR2)
•The table illustrates
some of the format
elements available to use
with TO_CHAR functions
SELECT TO_CHAR(salary,
'$99,999') AS "Salary"
FROM employees;

Tuesday, March 24, 2026 CPAN 121 SQL 71

Number Conversion to Character
Data (VARCHAR2)
•Can you identify the
format models used to
produce the following
output?
◦ $3000.00
◦ 4,500
◦ 9,000.00
◦ 0004422

Tuesday, March 24, 2026 CPAN 121 SQL 72

Number Conversion to Character
Data (VARCHAR2)
•Answers

Tuesday, March 24, 2026 CPAN 121 SQL 73

Character Conversion to Number
•It is often desirable to convert a character string to a number. The function for
this conversion is:
TO_NUMBER(character string, 'format model')

•The format model is optional, but should be included if the character string
being converted contains any characters other than numbers
•You cannot reliably perform calculations with character data

SELECT TO_NUMBER('5,320', '9,999')

AS "Number"
FROM dual

Tuesday, March 24, 2026 CPAN 121 SQL 74

Character Conversion to Number
•The bonus column includes data which contains 4 characters, the format model
specifies 3 characters, so an error is returned

SELECT last_name, TO_NUMBER(bonus, '999')

FROM employees
WHERE department_id = 80;

SELECT last_name, TO_NUMBER(bonus, '9999')

AS "Bonus"
FROM employees
WHERE department_id= 80;

Tuesday, March 24, 2026 CPAN 121 SQL 75

Character Conversion to Date
•To convert a character string to a date format, use:

TO_DATE('character string', 'format model')

•This conversion takes a non-date value character string such as "November 3,

2001" and converts it to a date value
•The format model tells the server what the character string "looks like":
TO_DATE('November 3, 2001', 'Month dd, yyyy')

•will return 03-Nov-2001

Tuesday, March 24, 2026 CPAN 121 SQL 76

Character Conversion to Date
•When making a character-to-date conversion, the fx( format exact) modifier
specifies exact matching for the character argument and the date format model
•In the following example, note that "May10" has no space between ''May" and
"10"
•The fx format model matches the character argument as it also has no space
between "Mon" and "DD"

Tuesday, March 24, 2026 CPAN 121 SQL 77

fx Modifier Rules
•The fx modifier rules are:
◦ Punctuation and quoted text in the character argument must match the
corresponding parts of the format model exactly (except for case)
◦ The character argument cannot have extra blanks Without fx, the Oracle
Server ignores extra blanks
◦ Numeric data in the character argument must have the same number of digits
as the corresponding element in the format model
◦ Without fx, numbers in the character argument can omit leading zeros

Tuesday, March 24, 2026 CPAN 121 SQL 78

fx Modifier Rules

Tuesday, March 24, 2026 CPAN 121 SQL 79

RR Date Format and YY Date
Format
•All date data should now be stored using four-digit years (YYYY)
•Some legacy databases however may still use the two-digit (YY) format
•It has not been that long since the century changed from 1900 to 2000
•Along with this change came considerable confusion as to whether a date
written as 02-Jan-98 would be interpreted as January 2, 1998 or January 2, 2098

Tuesday, March 24, 2026 CPAN 121 SQL 80

RR Date Format and YY Date
Format
•If the data being converted from character data to date data contains only a
two-digit year, Oracle has a way of interpreting these dates in the correct
century
•For example: '27-Oct-95’

•The two-digit year is interpreted as 2095, this may not be what was intended

Tuesday, March 24, 2026 CPAN 121 SQL 81

RR Date Format and YY Date
Format
•If YY is used in the format model, the year is assumed to be in the current
century
•If the two-digit year is not in the current century, we use RR

•The two-digit year is now interpreted as 1995

Tuesday, March 24, 2026 CPAN 121 SQL 82

A Few Simple Rules
•If the date format is specified with
the RR format, the return value has
two possibilities, depending on the
current year
•If the current year is between 00-
49:Dates from 0-49:
• The date will be in the current
century
• Dates from 50-99: The date will be
in the last century

Tuesday, March 24, 2026 CPAN 121 SQL 83

A Few Simple Rules
•If the current year is between 50-
99:Dates from 0-49:
◦ The date will be in next century
◦ Dates from 50-99: The date will be in
current century

Tuesday, March 24, 2026 CPAN 121 SQL 84

A Few Simple Rules
•The table below gives some examples of how YY and RR are interpreted,
depending on the current year

Tuesday, March 24, 2026 CPAN 121 SQL 85

A Few Simple Rules
•When I query my employee database using the following statement, it returns
every row in the table
•I know there are only a few employees who were hired before 1990

•As the format model in the WHERE clause uses YY, and the current year is 2017,
the query returns rows with a hire_date less than 2090

Tuesday, March 24, 2026 CPAN 121 SQL 86

Terminology – This Portion
•Key terms used in this lesson included:
• CHAR
• DATE
• DD date format
• Conversion function
• fm
• NUMBER

Tuesday, March 24, 2026 CPAN 121 SQL 87

Terminology – This Portion
•Key terms used in this lesson included: RR date format
• TO_CHAR
• TO_DATE
• TO_NUMBER
• VARCHAR2
• Fx Modifier

Tuesday, March 24, 2026 CPAN 121 SQL 88

Summary – This Portion
•In this lesson, you should have learned how to:
◦ Provide an example of an explicit data-type conversion and an implicit data-type
conversion
◦ Explain why it is important, from a business perspective, for a language to have built-
in data-conversion capabilities
◦ Construct a SQL query that correctly applies TO_CHAR, TO_NUMBER and TO_DATE
single-row functions to produce a desired result

Tuesday, March 24, 2026 CPAN 121 SQL 89

Summary – This Portion
•In this lesson, you should have learned how to:
◦ Apply the appropriate date and/or character format model to produce a desired
output
◦ Explain and apply the use of YY and RR to return the correct year as stored in the
database

Tuesday, March 24, 2026 CPAN 121 SQL 90

Functions Pertaining to NULL
Values
•Imagine this question: Is it true that X = Y?
•In order to answer you have to know the values of X and Y.
•Oracle has four general functions that pertain to the use of null values.
•The four functions are:
◦ NVL
◦ NVL2
◦ NULLIF
◦ COALESCE

Tuesday, March 24, 2026 CPAN 121 SQL 91

NVL Function
•The NVL function converts a null value to a known value of a fixed data type,
either date, character, or number.
•The data types of the null value column and the new value must be the same.
•The NVL function is:

NVL (expression 1 value that may contain a null, expression 2

value to substitute for null)

Tuesday, March 24, 2026 CPAN 121 SQL 92

NVL Function
•The following query uses the NVL function with character data types:
SELECT country_name, NVL(internet_extension, 'None')
AS "Internet extn"
FROM wf_countries
WHERE location = 'Southern Africa'
ORDER BY internet_extension DESC;

Null values are replaced with the text 'None'.

Tuesday, March 24, 2026 CPAN 121 SQL 93

NVL2 Function
•The NVL2 function evaluates an expression with three values.
•If the first value is not null, then the NVL2 function returns the second
expression.
•If the first value is null, then the third expression is returned.
•The values in expression 1 can have any data type.
•Expression 2 and expression 3 can have any data type except LONG.
•The data type of the returned value is always the same as the data type of
expression 2, unless expression 2 is character data, in which case the returned
type is VARCHAR2

Tuesday, March 24, 2026 CPAN 121 SQL 94

NVL2 Function
•The NVL2 function shown uses number data types for expressions 1, 2 and 3.

SELECT last_name, salary,

NVL2(commission_pct, salary + (salary * commission_pct), salary) AS income
FROM employees
WHERE department_id IN(80,90);

Tuesday, March 24, 2026 CPAN 121 SQL 95

Terminology – This Portion
•Key terms used in this lesson included:
• NVL
• NVL2

Tuesday, March 24, 2026 CPAN 121 SQL 96

Summary – This Portion
•In this lesson, you should have learned how to:
• Use the NVL function to show some text in case of a NULL value
• Use the NVL2 function to show a value in either case of a NULL value or a non-NULL
value

Tuesday, March 24, 2026 CPAN 121 SQL 97