Single Row Functions
Lecture 02
Agenda
• Character Functions
• Numeric Functions
• Datetime Functions
• Conversion Functions
• General Comparison Functions
SQL Functions
• There are two types of functions:
Single-row
Multiple-row
• A single-row function returns one result for each row. These functions operate on
single rows only and return one result for every row acted on.
• There are different types of Single-Row functions as follows:
Character
Number
Date
Conversion
General
• A multiple-row function returns one result per set of rows. Functions can
manipulate groups of rows to give one result per group of rows. These functions
are also called group functions.
Single-Row Functions
• Single-row functions return a single result for each row in the result set.
• Single-row functions can be used in
SELECT
WHERE
HAVING
ORDER BY
• Functions can be used to
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 may take arguments and always return a value.
Single-Row functions
• These functions manipulate data items.
Be a set to one or more arguments and return a single value for each row that is
retrieved by the query.
An argument can be one of the following:
User supplied constant
Variable value
Column name
Expression
• The actions of single row functions include:
Acts on each row that is returned by the query
Returns one result per row
May possibly return a different data type than the one that is referenced
The function expects one or more arguments
Character Functions
Character Functions
• These functions accept character type arguments and return character or
numeric values.
• Character functions:
Case manipulation
LOWER
UPPER
INITCAP
Character manipulation
SUBSTR
CONCAT
LENGTH
INSTR
TRIM
REPLACE
Case Manipulation Functions
Function Returning result
LOWER(‘DATABASE Systems’) database systems
UPPER (‘database sYstems’) DATABASE SYSTEMS
INITCAP(‘database Systems’) Database Systems
• LOWER()
returns the character argument with all lower case letters.
• UPPER()
returns the character argument with all upper case letters.
• INITCAP()
returns each world with the first letter capital and other letters lower case.
Words are delimited by white space or characters that are not alphanumeric.
Case Manipulation Functions (SQL Example 1)
SELECT LOWER(job_title) AS "Lower",
UPPER(first_name) AS ”Upper",
INITCAP(email) AS "InitCap"
FROM employees
WHERE manager_id = 2;
Case Manipulation Functions (SQL Example 2)
• The following SQL query returns employees whose first name start with
letters “elli” (case-insensitive).
• To make sure finding all matching employees, you can use LOWER() or
UPPER() functions on the left hand side of the comparison expression and
the matching pattern with all letters in lowercase or uppercase on the right
hand side of the comparison expression.
select *
FROM employees
WHERE LOWER(first_name) LIKE 'elli%';
Character Manipulation Functions
Function Returning result
CONCAT(‘Database’, ‘Systems’) DatabaseSystems
SUBSTR (‘DatabaseSystems’,1,4) Data
SUBSTR(‘DatabaseSystems’,-4,2) te
LENGTH(‘DatabaseSystems’) 15
INSTR(‘DatabaseSystems’, ‘b’) 5
LPAD(‘Tommy’, 10, ‘*’) *****Tommy
RPAD(‘Tommy’, 10, ‘*’) Tommy*****
REPLACE(‘Jack and Jue’, ‘J’, ‘Bl’) Black and Blue
TRIM(‘a’ FROM ‘aabbccaa’) bbcc
Numeric Functions
Numeric Functions
Function Returning result
ROUND(5.678, 2) 5.68
TRUNC(5.678, 2) 5.67
MOD(10, 3) 1
ROUND()
• ROUND(v, n)
It receives two arguments.
v: is a value of any numeric data type.
n: is an integer value.
returns the argument value v rounded to n places to the right of the decimal point.
If you omit the second parameter n, the default value 0 will be considered for the
second argument, which mean the function returns ROUND(v,0).
If you use 0 or no value for the second argument, n is rounded to zero decimal
places.
ROUND(10.96) à 11
TRUNC()
• TRUNC(v, n)
truncates a number v to n decimal places.
TRUNC(15.193, 2) à 15.19
TRUNC(15.193, 3) à 15.193
TRUNC(15.193, 1) à15.1
• TRUNC(n)
truncate a number v to zero decimal places.
TRUNC(15.193) à 15
MOD()
• MOD(v, n)
n: dividend
v: divider
The function MOD() returns the remainder of v divided by n.
if n is 0, then it returns v.
• MOD(121,14) à 9
• MOD(25, 7) à 4
• The MOD() function is used to determine if a number is odd or even.
Numeric Function Example
SELECT product_id, MOD(product_id,7),
list_price, ROUND(list_price,1), ROUND(list_price),
TRUNC(list_price,1), TRUNC(list_price)
FROM products
WHERE list_price < 50
ORDER BY product_id;
Datetime Functions
Datetime
• The fields that apply to datetime are
CENTURY YEAR MONTH DAY HOUR MINUTE SECOND
19 87 06 17 17 10 43
• Standard input and output of DATE is RR-MM-DD
Valid Oracle dates are between January 1, 4712 B.C., and December 31, 9999 A.D.
which, actually, depends on settings of the NLS parameters
most of the time, you’ll need to format the date
dates are not stored in the database in this format. all the components of the date
and time are stored
Note: century or year stored as 4 digits even if displayed as 2
SELECT * FROM V$NLS_PARAMETERS;
Specify a DATE value
• You can specify a DATE value by:
• Specifying the date value as a literal
• Converting a character or numeric value to a date value with the TO_DATE function
DATE 'yyyy-mm-dd'
to_date('2022-DEC-25 17:30','YYYY-MON-DD HH24:MI')
SYSDATE
• SYSDATE returns current
Date
Time
SELECT SYSDATE
FROM dual;
Arithmetic Operations on Dates
• Since Oracle database stores dates as numbers, arithmetic operations such
as addition or subtraction can be performed on date values. You can add or
subtract both numbers and dates to or from date values.
Date + Number
Date – Number
Date – Date
• Example:
The next billing due date is calculated which 30 days from today.
SELECT sysdate, sysdate + 30
FROM dual;
Dates and Arithmetic Operators
• Example: Find how many weeks an employee has worked at the company?
SELECT last_name, (sysdate - hire_date)/7 "Weeks Employed" -- returns days convert to weeks
FROM employees
WHERE manager_id= 2;
• To improve the result format, we use function TRUNC()
SELECT last_name, TRUNC((sysdate - hire_date)/7,2) as "Weeks Employed"
FROM employees
WHERE manager_id = 2;
