Welcome to
CST 2002:
DATABASE DESIGN AND
IMPLEMENTATION LECTURE 7
READINGS –
WEEK 9
Chapter 10
EXAM: SINGLE-
ROW FUNCTIONS
We do not expect you to know
all the single-row functions by rote for
the exam
The following are the most
important for the exam:
SUBSTR, ROUND, TRUNC,
MONTHS_BETWEEN, ADD_MONTHS,
TO_DATE, TO_CHAR, SYSDATE, NVL
Terminology
Function – predefined block of
code that accepts arguments
Single-row function – returns
one row of results for each record
processed
Multiple-row function – returns
one result per group of data processed
(covered in the next chapter)
Types of Functions
Oracle 10g: SQL
Case Conversion
Functions
• Case conversion functions alter
the case of data stored in a column or
character string:
Used in a SELECT clause they
alter the appearance of the data in the
results
Used in a WHERE clause they
alter the value for comparison
LOWER Function
• Used to convert characters to
lowercase letters
UPPER Function
• Used to convert characters to
uppercase letters
It can be used in the same
way as the LOWER function:
To affect the display of
characters it is used in a SELECT clause
To modify the case of
characters for a search condition it is used
in a WHERE clause
The syntax for the UPPER
function is UPPER(c)
Where c is the character string
or field to be converted into uppercase
characters
INITCAP Function
• Used to convert characters to
mixed case
Character
Manipulation
Functions
• Character manipulation functions
manipulate data by extracting
substrings, counting the number of
characters, replacing strings, etc.
SUBSTR Function
• Used to return a substring, or portion
of a string
INSTR Function
Nesting Functions
LENGTH Function
• Used to determine the number of
characters in a string
LPAD and RPAD Func
tions
• Used to pad, or fill in, a character
string to a fixed width
LTRIM and RTRIM Fun
ctions
• Used to remove a specific string of
characters
REPLACE Function
• Substitutes a string with another
specified string
TRANSLATE
Function
CONCAT Function
• Used to concatenate two character
strings
Number Functions
• Allow for manipulation of numeric
data:
ROUND
TRUNC
MOD
ABS
ROUND Function
• Used to round numeric columns to a
stated precision
TRUNC Function
• Used to truncate a numeric value to a
specific position
MOD Function
ABS Function
Date Functions
Used to perform date calculations
or format date values
Subtract date for number of days
difference
MONTHS_BETWEE
N Function
• Determines the number of
months between two dates
ADD_MONTHS Fun
ction
• Adds a specified number of months to
a date
NEXT_DAY Function
• Determines the next occurrence
of a specified day of the week after a
given date
TO_DATE Function
• Converts various date formats
to the internal format (DD-MON-YY)
used by Oracle 10g
Format Model Eleme
nts - Dates
ROUND Function
TRUNC Function
Regular Expressions
Regular expressions allow the
description of complex patterns in
textual data
REGEXP_LIKE
Other Functions
NVL
NVL2
TO_CHAR
DECODE
SOUNDEX
NVL Function
• Substitutes a value for a NULL
value
NVL2 Function
• Allows different actions based on
whether a value is NULL
TO_CHAR Function
• Converts dates and numbers to
a formatted character string
Format Model Eleme
nts – Time and Numb
er
DECODE Function
• Determines action based upon values
in a list
SOUNDEX Function
• References phonetic
representation of words
TO_NUMBER
Function
DUAL Table
Dummy table
Consists of one column and
one row
Can be used for table
reference in the FROM clause
Using DUAL
