DBMS/SQL SQL (Single-row) Functions

Page 05-1
DBMS/SQL SQL (Single-row) Functions

Page 05-2
DBMS/SQL SQL (Single-row) Functions

SQL Functions:
So far, we have seen “Aggregate functions”, which operate against a
“collection of values”, however return a “single value”.
Now we shall see “scalar functions” which operate against a “single value”,
and return a “single value” based on the input value.
The functions can be broadly classified into:
Numerical functions- Accept numeric input and return numeric
values
For example: ABS, CEIL, TRUNC, ROUND, POWER, etc.
Character functions - Accept character input and can return both
characters and number values
For example: CONCAT, LPAD, RPAD, TRIM, SUBSTR, etc.
Date and Time functions- Operate on values of the DATE data type
For example: SYSDATE, ADD_MONTHS, LAST_DAY, etc.
Conversion functions- Convert a value from one data type to another
For example: CAST, ASCIISTR, ROWIDTOCHAR, etc.
Miscellaneous Single-row functions
For example: BFILENAME, DECODE, NVL, NULLIF,
USERENV, etc.

Page 05-3
DBMS/SQL SQL (Single-row) Functions

Let us now have a look at each of these function categories

Page 05-4
DBMS/SQL SQL (Single-row) Functions

Numeric Functions:
TRUNC(n,m)
The trunc function returns a number truncated to a certain number of
decimal places. The syntax for the trunc function is:

trunc( number, [ decimal_places ] )

where:
number = the number to truncate.
decimal_places = the number of decimal places to truncate to. This value
must be an integer. If this parameter is omitted, the trunc function will
truncate the number to 0 decimal places.
For example:
trunc(125.815) would return 125
trunc(125.815, 1) would return 125.8
trunc(125.815, -1) would return 120
trunc(125.815, -2) would return 100
ROUND(n,m)
The round function returns a number rounded to a certain number of
decimal places. The syntax for the round function is:
round( number, [ decimal_places ] )
Page 05-5
DBMS/SQL SQL (Single-row) Functions

Numeric Functions (contd.):

where:
number = the number to round.
decimal_places = the number of decimal places rounded to. This value must
be an integer. If this parameter is omitted, the round function will round the
number to 0 decimal places.
For example:
round(125.315) would return 125
round(125.315, 0) would return 125
round(125.315, 1) would return 125.3
round(125.315, 2) would return 125.32
round(-125.315, 2) would return -125.32
CEIL(n)
It returns smallest integer greater than or equal to n.

SELECT CEIL(15.7) "Ceiling" FROM DUAL;

Ceiling
16

FLOOR(n)
•It returns largest integer equal to or less than n.

SELECT FLOOR(15.7) "Floor" FROM DUAL;

Floor
15

ABS(n)
•It returns the absolute value of n.

SELECT ABS(20) "Absolute" FROM DUAL;

Absolute
20

POWER function
•It returns m raised to nth power .It is of the form: Power(m,n)

SELECT POWER (3,3) “Raised” FROM DUAL;

Raised
27

Page 05-6
DBMS/SQL SQL (Single-row) Functions

Examples of Number Functions:

DUAL table, which is shown in the slide, is a table owned by SYS.
SYS owns the “data dictionary”, and DUAL is part of the data
dictionary.
DUAL is a small work-table, which consists of only one row and one
column, and contains the value “x” in that column. Besides
arithmetic calculations, it also supports “date retrieval” and it’s
“formatting”.
Often a simple calculation needs to be done. A SELECT must have
a table name in it’s FROM clause, else it fails.
To facilitate such calculations via a SELECT, the DUAL dummy
table is provided.
The structure of the DUAL table can be viewed by using the SQL
statement:

DESC DUAL;

Page 05-7
DBMS/SQL SQL (Single-row) Functions

Examples of Number (numeric) Functions (contd.):

Round(n,m):

SELECT ROUND(17.175,-1) "Number"

FROM dual;

O/P : 20

TRUNC(n,m):

SELECT TRUNC(15.81,-1) "Number“

FROM dual;

O/P : 10

Page 05-8
DBMS/SQL SQL (Single-row) Functions

Character Functions:
Example1:

SELECT Upper(‘Hello’), Lower(‘WORLD’)

FROM Dual;

Note: Functions can be nested to any depth. Evaluation starts with the
“inner most functions”, and proceeds outwards.
For example: LENGTH(LTRIM(RTRIM(name)))

Page 05-9
DBMS/SQL SQL (Single-row) Functions

Examples of Character Functions:

REPLACE function returns char with every occurrence of search_string replaced
with replacement_string.
If replacement_string is omitted or NULL, then all occurrences of
search_string are removed.
If search_string is NULL, then char is returned.

SELECT REPLACE('JACK and JUE','J','BL') "Changes"

FROM DUAL;

Changes
BLACK and BLUE

CONCAT function returns “char1” concatenated with “char2”. Both “char1” and
“char2” can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2,
CLOB, or NCLOB. The string returned is in the same character set as char1. It’s
datatype depends on the datatypes of the arguments.
In concatenations of two different datatypes, the Oracle Database returns
the datatype that results in a “lossless conversion”. Therefore:
If one of the arguments is a LOB, then the returned value is a LOB.
If one of the arguments is a national datatype, then the returned
value is a national datatype.

Page 05-10
DBMS/SQL SQL (Single-row) Functions

String Functions:
• The CONCAT function is equivalent to the “concatenation operator (||)”.
The function is useful when there are spaces in the values to be
concatenated. The “concatenation operator” does not permit spaces.
UPPER(string)
• This function converts all characters in string to uppercase.

SELECT UPPER(staff_name),staff_code
FROM staff_master;

LOWER(string)
• This function converts all characters in string to lowercase.

SELECT LOWER(student_name)
FROM student_master;

INITCAP(string):
• This function converts the first character of each word in string to
uppercase and the rest of the characters to lowercase.

SELECT INITCAP(staff_name)
FROM staff_master;

LPAD(string1,n,string2)
• This function adds string2 before string1 as many times as required to
make the string1 length equal to “n” chars.
• To right align the names of staff_members:
SELECT LPad(staff_name,30)
FROM staff_master;
LTRIM(string,CHAR set)
• This function removes chars from beginning of string as long as the
character matches one of the chars in the CHAR set.

SELECT student_name,LTRIM(student_name,'MALICE')
FROM student_master;

RPAD(string1,n,string2)
• This function is similar to LPAD. It adds chars to the right end.
RTRIM(string,CHAR set)
• This function is similar to LTRIM. It removes chars from the right end.
SUBSTR(CHAR,m,n)
• This function returns the string from the mth character of the string to the
nth character

Page 05-11
DBMS/SQL SQL (Single-row) Functions

String Functions (contd.):

• LENGTH function
It returns the length of char in characters
It is of the form:
Length (string)

Select length (‘candide’) “ length in characters”

FROM dual;

INSTR(string, pattern[ , start [,occurrence ] ] )

• It returns the location of a character IN a STRING.

SELECT INSTR (‘CORPORATEFLOOR’,’R’,3,2) “Instring”

FROM DUAL

Instring
6

Page 05-12
DBMS/SQL SQL (Single-row) Functions

DATE Functions
Datetime functions operate on date (DATE), timestamp, and interval values.
Some of the datetime functions were designed for the Oracle DATE
datatype (ADD_MONTHS, CURRENT_DATE, LAST_DAY, and
NEXT_DAY).
If you provide a timestamp value as their argument, Oracle
Database internally converts the input type to a DATE value and
returns a DATE value.
The exceptions are:
the MONTHS_BETWEEN function, which returns a number,
and
the ROUND and TRUNC functions, which do not accept
timestamp or interval values at all.
The remaining datetime functions are designed to accept any of the three
types of data (date, timestamp, and interval), and to return a value of one of
these types.

Page 05-13
DBMS/SQL SQL (Single-row) Functions

Date Functions:
ADD_MONTHS(DATE1,int1) returns the date as addition of “date” and
“integer” months. The first argument can be a datetime value or any value
that can be implicitly converted to DATE. The second argument can be an
integer or any value that can be implicitly converted to an integer. The
return type is always DATE.

SELECT book_code,ADD_MONTHS(book_issue_date,1)
FROM book_transaction;

MONTHS_BETWEEN (DATE1,DATE2)This function returns number of

months between the two DATEs. The result is positive if date1 is later than
date2 and result is negative if date2 is later than date2.

SELECT staff_code,
MONTHS_BETWEEN(TRUNC(sysdate),hiredate)
FROM staff_master;

Page 05-14
DBMS/SQL SQL (Single-row) Functions

Date Functions (contd.):

LASTDAY(date1) returns the date of the last day of the month that contains the
date. The return type is always DATE.
To display the date of the last day in the month the books were issued

SELECT book_issue_date, LAST_DAY(book_issue_date)

FROM book_transaction;

NEXT_DAY(date1, char) Returns the date of the first weekday specified as char
that is later the given date
To display the date on coming Friday in the week that books were issued
SELECT book_issue_date,
NEXT_DAY(book_issue_date,’Friday’)
FROM book_transaction

CURRENT_DATE & CURRENT_TIMESTAMP return current date and time

respectively base on the timezone set for the database.
The query given on the slide above shows the date and time based on the offset
time base on GMT which is according to the timezone set for the database

Page 05-15
DBMS/SQL SQL (Single-row) Functions

Date Functions (contd.):

EXTRACT(datetime) extracts the value of a specified datetime field. This
function is useful for manipulating datetime field values. For example you
can extract only year, month or day from a given date value.
To display year of birth for all students

SELECT EXTRACT(year from student_dob)

FROM student_master

TRUNC (DATE1)
This function truncates the time part from the DATE. This is required when
we do DATE calculations.

SELECT staff_name, TRUNC(sysdate) -

TRUNC(HIREDATE) FROM staff_master;

Page 05-16
DBMS/SQL SQL (Single-row) Functions

Given below are formats of Date Functions

Format Meaning
YYYY Four digit year
YY Last two digits of the year
MM Month (01-12 where JAN = 01 …)
MONT Name of the month stored as a length of nine characters.
H
MON Name of the month in three letter format.
DD Day of the month (01-31).
D Day of the week.
DAY Name of the day stored as a length of nine characters.
DY Name of the day in three letter format.
FM This prefix can be added to suppress blank padding.
HH Hour of the day.
HH24 Hour in the 24 hour format.
MI Minutes of the hour.
SS Seconds of the minute.
TH The suffix used with the day.

Page 05-17
DBMS/SQL SQL (Single-row) Functions

Conversion Functions
Conversion functions convert values of one datatype to another. Usually the
conversion function accepts two arguments wherein first is the input type
and second is the output type.
Oracle takes care of implicit datatype conversion. Explicit datatype
conversions are done using the conversion functions.
Although Oracle does provide implicit type conversion to ensure reliability of
SQL statements you should use conversion functions

Page 05-18
DBMS/SQL SQL (Single-row) Functions

TO_CHAR(DATE1,format)
This function converts the DATE given to the format specified.

SELECT TO_CHAR(TRUNC(sysdate), ‘ddth fmMonth yy’)

'DATE'
FROM dual;

DATE
01st January 95

SELECT TO_CHAR(TRUNC(sysdate),'fmMonth') "DATE“

FROM dual;

SELECT to_char ( sysdate , ’Q’)

FROM dual ;

DATE
July
Example: To display the quarter which has the specified date.

Page 05-19
DBMS/SQL SQL (Single-row) Functions

Page 05-20
DBMS/SQL SQL (Single-row) Functions

Miscellaneous Functions
These functions are sometimes also called as General Functions. These
functions work with any datatype values

Page 05-21
DBMS/SQL SQL (Single-row) Functions

Miscellaneous Single-row Functions:

NVL ()
Many times there are records holding NULL values in a table. When an
output of such a table is displayed, it is difficult to understand the reason of
NULL or BLANK values shown in the output.
The only way to overcome this problem is to replace NULL values with
some other meaningful value while computing the records. This can be
done using the NVL function.
NVL2()
The NVL2 function can be thought of as an extension to NVL function. But
this function examines the first value. If the value is not null then returns the
second value and if null then returns the third value.

Examples for both functions are shown on the slide

Page 05-22
DBMS/SQL SQL (Single-row) Functions

NullIF ()
This function compares the arguments provided. If they are equal, the
function returns null and if not then it returns the first argument.
You cannot specify the literal NULL as the first argument.

COALESE()
The Coalesce function returns the first non null value is the given argument
list. The benefit of using this function versus NVL() us that it can take
multiple alternate values.

Examples for both the functions are shown on the slide

Page 05-23
DBMS/SQL SQL (Single-row) Functions

CASE()
In a simple case expression, Oracle searches for the first WHEN…THEN
pair for which expr is equal to comparison expr and returns return_expr.
The expressions used should be of same datatype.
CASE is capable of more logical comparisons like <> etc..
Also CASE can work with predicates and subqueries

Page 05-24
DBMS/SQL SQL (Single-row) Functions

Page 05-25
DBMS/SQL SQL (Single-row) Functions

DECODE () function
This function decodes the expression after comparing it to each search
value. If the expression is the same as search, result is returned. If the
default value is omitted, a null value is returned where a search value does
not match any of the result values.
As compared to CASE, DECODE can do an equality check only. The
expressions in DECODE can work only on scalar values.
DECODE can work as a function inside SQL only but CASE can be more
efficient substitute in PL/SQL blocks.

Page 05-26
DBMS/SQL SQL (Single-row) Functions

Tips and Tricks:

If possible, try avoiding the SUBSTRING function in your WHERE clauses.
Depending on how it is constructed, using the SUBSTRING function can
force a table scan instead of allowing the Optimizer to use an Index
(assuming there is one).
If the substring you are searching for does not include the first
character of the column you are searching for, then a table scan is
performed.
In case of conversion functions, If value to be converted is not in right
format, then Oracle will throw an error

Page 05-27
DBMS/SQL SQL (Single-row) Functions

Page 05-28
DBMS/SQL SQL (Single-row) Functions

Page 05-29
DBMS/SQL SQL (Single-row) Functions

Page 05-30
DBMS/SQL SQL (Single-row) Functions

Page 05-31