Single-Row Functions

Single-Row Functions

Objectives
After completing this lesson, you should be able to
do the following:
• Describe various types of functions available
in SQL
• Use character, number, and date functions in
SELECT statements
• Describe the use of conversion functions

SQL Functions
Functions are a very powerful feature of SQL and can be used to do the following:
• Perform calculations on data
• Modify individual data items
• Manipulate output for groups of rows
• Format dates and numbers for display
• Convert column data types

SQL functions sometimes take arguments and always return a value.

SQL Functions (continued)
There are two distinct types of functions:
• Single-row functions
• Multiple-row functions
Single-Row Functions
These functions operate on single rows only and return one result per row. There are different types of
single-row functions. This lesson covers the following ones:
• Character
• Number
• Date
• Conversion
Multiple-Row Functions
Functions can manipulate groups of rows to give one result per group of rows. These functions are
known as group functions. This is covered in a later lesson.

Single-Row Functions
Single row functions:
• Manipulate data items
• Accept arguments and return one value
• Act on each row returned
• Return one result per row
• May modify the data type
• Can be nested
• Accept arguments which can be a column or an
expression
function_name [(arg1, arg2,...)]

Single-Row Functions
Single-row functions are used to manipulate data items. They accept one or more arguments and
return one value for each row returned by the query. An argument can be one of the following:
• User-supplied constant
• Variable value
• Column name
• Expression
Features of single-row functions include:
• Acting on each row returned in the query
• Returning one result per row
• Possibly returning a data value of a different type than that referenced
• Possibly expecting one or more arguments
• Can be used in SELECT, WHERE, and ORDER BY clauses; can be nested
In the syntax:
function_name is the name of the function.
arg1, arg2 is any argument to be used by the function. This can be represented by a

column name or expression.


Single-Row Functions (continued)
This lesson covers the following single-row functions:
• Character functions ccept character input and can return both character and number values
• Number functions Accept numeric input and return numeric values
• Date functions Operate on values of the DATE data type (All date functions return a value of
DATE data type except the MONTHS_BETWEEN function, which returns a number.)
• Conversion functions Convert a value from one data type to another
• General functions:
– NVL
– NVL2
– NULLIF
– COALSECE
– CASE

– DECODE


Character Functions
Single-row character functions accept character data as input and can return both character and
numeric values. Character functions can be divided into the following:
• Case-manipulation functions

• Character-manipulation functions

Case Manipulation Functions
LOWER, UPPER, and INITCAP are the three case-conversion functions.
• LOWER Converts mixed case or uppercase character strings to lowercase
• UPPER Converts mixed case or lowercase character strings to uppercase
• INITCAP Converts the first letter of each word to uppercase and remaining letters to lowercase
SELECT 'The job id for '||UPPER(last_name)||' is '
||LOWER(job_id) AS "EMPLOYEE DETAILS"

FROM employees;

Using Case Manipulation Functions
Display the employee number, name, and department
number for employee Higgins:
SELECT employee_id, last_name, department_id
FROM employees
WHERE last_name = ’higgins’;
no rows selected
SELECT employee_id, last_name, department_id
FROM employees

WHERE LOWER(last_name) = ’higgins’;

Case Manipulation Functions (continued)
The slide example displays the employee number, name, and department number of employee Higgins.
The WHERE clause of the first SQL statement specifies the employee name as higgins. Because all the
data in the EMPLOYEES table is stored in proper case, the name higgins does not find a match in the
table, and no rows are selected.
The WHERE clause of the second SQL statement specifies that the employee name in the EMPLOYEES
table is compared to higgins, converting the LAST_NAME column to lowercase for comparison
purposes. Since both names are lowercase now, a match is found and one row is selected. The WHERE
clause can be rewritten in the following manner to produce the same result:
...WHERE last_name = ’Higgins’
The name in the output appears as it was stored in the database. To display the name capitalized, use the
UPPER function in the SELECT statement.
SELECT employee_id, UPPER(last_name), department_id
FROM employees

WHERE INITCAP(last_name) = ’Higgins’;


Character Manipulation Functions
CONCAT, SUBSTR, LENGTH, INSTR, LPAD, RPAD, and TRIM are the character manipulation
functions covered in this lesson.
• CONCAT Joins values together (You are limited to using two parameters with CONCAT.)
• SUBSTR Extracts a string of determined length
• LENGTH Shows the length of a string as a numeric value
• INSTR Finds numeric position of a named character
• LPAD Pads the character value right-justified
• RPAD: Pads the character value left-justified
• TRIM: Trims heading or trailing characters (or both) from a character string (If
trim_character or trim_source is a character literal, you must enclose it in single

quotes.)


Character-Manipulation Functions (continued)
The slide example displays employee first names and last names joined together, the length of the
employee last name, and the numeric position of the letter a in the employee last name for all
employees who have the string REP contained in the job ID starting at the fourth position of the job
ID.
Example
Modify the SQL statement on the slide to display the data for those employees whose last names end
with an n.
SELECT employee_id, CONCAT(first_name, last_name) NAME,
LENGTH (last_name), INSTR(last_name, ’a’) "Contains ’a’?"
FROM employees

WHERE SUBSTR(last_name, -1, 1) = ’n’;

Number Functions
• ROUND: Rounds value to specified decimal
ROUND(45.926, 2)-------------------- 45.93
• TRUNC: Truncates value to specified decimal
TRUNC(45.926, 2) --------------------45.92
• MOD: Returns remainder of division

MOD(1600, 300)----------------------- 100


ROUND Function
The ROUND function rounds the column, expression, or value to n decimal places. If the second
argument is 0 or is missing, the value is rounded to zero decimal places. If the second argument is 2,
the value is rounded to two decimal places. Conversely, if the second argument is -2, the value is
rounded to two decimal places to the left.
The ROUND function can also be used with date functions. You will see examples later in this lesson.
The DUAL Table
The DUAL table is owned by the user SYS and can be accessed by all users. It contains one column,
DUMMY, and one row with the value X. The DUAL table is useful when you want to return a value once
only, for instance, the value of a constant, pseudocolumn, or expression that is not derived from a table
with user data. The DUAL table is generally used for SELECT clause syntax completeness, because
both SELECT and FROM clauses are mandatory, and several calculations do not need to select from

actual tables.


TRUNC Function
The TRUNC function truncates the column, expression, or value to n decimal places.
The TRUNC function works with arguments similar to those of the ROUND function. If the second
argument is 0 or is missing, the value is truncated to zero decimal places. If the second argument is 2,
the value is truncated to two decimal places. Conversely, if the second argument is -2, the value is
truncated to two decimal places to the left.

Like the ROUND function, the TRUNC function can be used with date functions.

Using the MOD Function
Calculate the remainder of a salary after it is divided
by 5000 for all employees whose job title is sales

representative.

SELECT last_name, salary, MOD(salary, 5000)
FROM employees

WHERE job_id = ’SA_REP’;

MOD Function
The MOD function finds the remainder of value1 divided by value2. The slide example calculates the

remainder of the salary after dividing it by 5,000 for all employees whose job ID is SA_REP.

Working with Dates
• Oracle database stores dates in an internal
numeric format: century, year, month, day, hours,
minutes, seconds.
• The default date display format is DD-MON-RR.
– Allows you to store 21st century dates in the 20th
century by specifying only the last two digits of the
year.
– Allows you to store 20th century dates in the 21st

century in the same way.

SELECT last_name, hire_date
FROM employees

WHERE last_name like ’G%’;

Summary

In this lesson, you should have learned how to:

• Perform calculations on data using functions

• Modify individual data items using functions

• Manipulate output for groups of rows using

functions