Single-Row Functions
Course Code: CSC 2108 Course Title: Introduction To Database
Department of Computer Science
Faculty of Science and Technology
Lecture No: 06 Week No: 03 Semester: spg 22-23
Lecturer: MD SAJID BIN- FAISAL
Lecture Outline
SQL Functions
Types of SQL Functions
Single-Row Functions
o Character Functions
o Number Functions
o Date Functions
o Conversion Functions
• TO_NUMBER
• TO_CHAR
• TO_DATE
o General Functions
• NVL Function
• NVL2 Function
• NULLIF Function
• Decode Function
SQL Functions
Input Output
Function
arg 1 Function
performs action
arg 2
Result
value
arg n
Two Types of SQL Functions
Functions
Single-row Multiple-row
functions 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 datatype
•Can be nested
function_name
function_name (column|expression,
(column|expression, [arg1,
[arg1, arg2,...])
arg2,...])
Single-Row Functions
Character
General Number
Single-row
functions
Conversion Date
Character Functions
Character
functions
Case conversion Character manipulation
functions functions
CONCAT
LOWER Trim
SUBSTR Ltrim, Rtrim
UPPER LENGTH Replace
INSTR
INITCAP
LPAD, RPAD
Case Conversion Functions
Convert case for character strings
Function Result
LOWER('SQL Course') sql course
UPPER('SQL Course') SQL COURSE
INITCAP('SQL Course') Sql Course
Using Case Conversion Functions
Display the employee number, name, and department number for employee
Blake.
SQL>
SQL> SELECT
SELECT empno,
empno, ename,
ename, deptno
deptno
22 FROM
FROM emp
emp
33 WHERE
WHERE ename
ename == 'blake';
'blake';
no
no rows
rows selected
selected
SQL> SELECT empno, ename, deptno
2 FROM emp
3 WHERE LOWER(ename) = 'blake';
EMPNO
EMPNO ENAME
ENAME DEPTNO
DEPTNO
---------
--------- ---------- ---------
---------- ---------
7698
7698 BLAKE
BLAKE 30
30
Character Manipulation Functions
Manipulate character strings
Function Result
CONCAT('Good', 'String') GoodString
SUBSTR('String',1,3) Str
LENGTH('String') 6
INSTR('String', ‘r') 3
Trim(‘S’ from ‘SSMITH’) MITH
Replace(‘toy’,’y’,’let’) tolet
Using the Character Manipulation
Functions
SQL> SELECT ename, CONCAT (ename, job), LENGTH(ename),
2 INSTR(ename, 'A')
3 FROM emp
4 WHERE SUBSTR(job,1,5) = 'SALES';
ENAME CONCAT(ENAME,JOB) LENGTH(ENAME) INSTR(ENAME,'A')
---------- ------------------- ------------- ----------------
MARTIN MARTINSALESMAN 6 2
ALLEN ALLENSALESMAN 5 1
TURNER TURNERSALESMAN 6 0
WARD WARDSALESMAN 4 2
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
Using the ROUND Function
SQL> SELECT ROUND(45.923,2), ROUND(45.923,0),
2 ROUND(45.923,-1)
3 FROM DUAL;
ROUND(45.923,2) ROUND(45.923,0) ROUND(45.923,-1)
--------------- -------------- -----------------
45.92 46 50
Using the TRUNC Function
SQL> SELECT TRUNC(45.923,2), TRUNC(45.923),
2 TRUNC(45.923,-1)
3 FROM DUAL;
TRUNC(45.923,2) TRUNC(45.923) TRUNC(45.923,-1)
--------------- ------------- ---------------
45.92 45 40
Using the MOD Function
Calculate the remainder of the ratio of salary to commission for all
employees whose job title is salesman.
SQL> SELECT ename, sal, comm, MOD(sal, comm)
2 FROM emp
3 WHERE job = 'SALESMAN';
ENAME SAL COMM MOD(SAL,COMM)
---------- --------- --------- -------------
MARTIN 1250 1400 1250
ALLEN 1600 300 100
TURNER 1500 0 1500
WARD 1250 500 250
Working with Dates
• Oracle stores dates in an internal numeric format:
century, year, month, day, hours, minutes, seconds.
• The default date format is DD-MON-YY.
• SYSDATE is a function returning date and time.
• DUAL is a dummy table used to view SYSDATE.
Arithmetic with Dates
• Add or subtract a number to or from a date for a
resultant date value.
• Subtract two dates to find the number of days between
those dates.
• Add hours to a date by dividing the number of hours by
24.
Using Arithmetic Operators with
Dates
SQL> SELECT ename, (SYSDATE-hiredate)/7 WEEKS
2 FROM emp
3 WHERE deptno = 10;
ENAME WEEKS
---------- ---------
KING 830.93709
CLARK 853.93709
MILLER 821.36566
Date Functions
Function Description
MONTHS_BETWEEN Number of months
between two dates
ADD_MONTHS Add calendar months to date
NEXT_DAY Next day of the date specified
LAST_DAY Last day of the month
ROUND Round date
TRUNC Truncate date
Using Date Functions
• MONTHS_BETWEEN ('01-SEP-95','11-JAN-94')
19.6774194
• ADD_MONTHS ('11-JAN-94',6) '11-JUL-94'
• NEXT_DAY ('01-SEP-95','FRIDAY') '08-SEP-95'
• LAST_DAY('01-SEP-95') '30-SEP-95'
Using Date Functions
Round(to_date(’25-jul-05’),’month’) 01-Aug-05
Round(to_date(’25-jul-05’),’Year’) 01-Jan-06
Trunc(to_date(’25-jul-05’),’month’) 01-Jul-05
Trunc(to_date(’25-jul-05’),’year’) 01-Jan-05
Conversion Functions
Datatype
conversion
Implicit datatype Explicit datatype
conversion conversion
Implicit Datatype Conversion
For assignments, the Oracle can automatically convert the following:
From To
VARCHAR2 or CHAR NUMBER
VARCHAR2 or CHAR DATE
NUMBER VARCHAR2
DATE VARCHAR2
Implicit Datatype Conversion
For expression evaluation, the Oracle Server can automatically convert the
following:
From To
VARCHAR2 or CHAR NUMBER
VARCHAR2 or CHAR DATE
Explicit Datatype Conversion
TO_NUMBER TO_DATE
NUMBER CHARACTER DATE
TO_CHAR TO_CHAR
TO_CHAR Function with Dates
TO_CHAR(date,
TO_CHAR(date, 'fmt')
'fmt')
The format model:
•Must be enclosed in single quotation marks and is case
sensitive
•Can include any valid date format element
•Has an fm element to remove padded blanks or suppress
leading zeros
•Is separated from the date value by a comma
Elements of Date Format Model
YYYY Full year in numbers
YEAR Year spelled out
MM Two-digit value for month
MONTH Full name of the month
Three-letter abbreviation of the
DY
day of the week
DAY Full name of the day
Elements of Date Format Model
Time elements format the time portion of the date.
HH24:MI:SS AM [Link] PM
Add character strings by enclosing them in double quotation marks.
DD "of" MONTH 12 of OCTOBER
Number suffixes spell out numbers.
ddspth fourteenth
Using TO_CHAR Function with Dates
SQL> SELECT ename,
2 TO_CHAR(hiredate, 'fmDD Month YYYY') HIREDATE
3 FROM emp;
ENAME HIREDATE
---------- -----------------
KING 17 November 1981
BLAKE 1 May 1981
CLARK 9 June 1981
JONES 2 April 1981
MARTIN 28 September 1981
ALLEN 20 February 1981
...
14 rows selected.
TO_CHAR Function with Numbers
TO_CHAR(number,
TO_CHAR(number, 'fmt')
'fmt')
Use these formats with the TO_CHAR function to display a number value as a
9
character:
Represents a number
0 Forces a zero to be displayed
$ Places a floating dollar sign
L Uses the floating local currency symbol
. Prints a decimal point
, Prints a thousand indicator
Using TO_CHAR Function with
Numbers
SQL> SELECT TO_CHAR(sal,'$99,999') SALARY
2 FROM emp
3 WHERE ename = 'SCOTT';
SALARY
--------
$3,000
TO_NUMBER and TO_DATE Functions
•Convert a character string to a number format using the
TO_NUMBER function
TO_NUMBER(char[,
TO_NUMBER(char[, 'fmt'])
'fmt'])
• Convert a character string to a date format using the
TO_DATE function
TO_DATE(char[,
TO_DATE(char[, 'fmt'])
'fmt'])
NVL Function
Converts null to an actual value
•Datatypes that can be used are date, character, and
number.
•Datatypes must match
NVL(comm,0)
NVL(hiredate,'01-JAN-97')
NVL(job,'No Job Yet')
Using the NVL Function
SQL> SELECT ename, sal, comm, (sal*12)+NVL(comm,0)
2 FROM emp;
ENAME SAL COMM (SAL*12)+NVL(COMM,0)
---------- --------- --------- --------------------
KING 5000 60000
BLAKE 2850 34200
CLARK 2450 29400
JONES 2975 35700
MARTIN 1250 1400 16400
ALLEN 1600 300 19500
...
14 rows selected.
NVL2(expr1,expr2,expr3)
If exp1 is not null it returns exp2
If exp1 is null it returns exp3
Example:
Select nvl2(comm,comm+sal,sal) from emp
NULLIF(expr1,expr2)
Compares 2 expressions and returns null if they are equal or the first
expression if they are not equal.
Example:
Select ename,length(ename),job,length(job),
NULLIF(ename,job) from emp;
DECODE Function
Facilitates conditional inquiries by doing the work of a CASE or IF-THEN-ELSE
statement
DECODE(col/expression,
DECODE(col/expression, search1,
search1, result1
result1
[,
[, search2,
search2, result2,...,]
result2,...,]
[,
[, default])
default])
Using the DECODE Function
SQL> SELECT job, sal,
2 DECODE(job, 'ANALYST', SAL*1.1,
3 'CLERK', SAL*1.15,
4 'MANAGER', SAL*1.20,
5 SAL)
6 REVISED_SALARY
7 FROM emp;
JOB SAL REVISED_SALARY
--------- --------- --------------
PRESIDENT 5000 5000
MANAGER 2850 3420
MANAGER 2450 2940
...
14 rows selected.
Nesting Functions
Single-row functions can be nested to any level.
Nested functions are evaluated from deepest level to the
least-deep level.
F3(F2(F1(col,arg1),arg2),arg3)
Step 1 = Result 1
Step 2 = Result 2
Step 3 = Result 3
Nesting Functions
SQL> SELECT ename,
2 NVL(TO_CHAR(mgr),'No Manager')
3 FROM emp
4 WHERE mgr IS NULL;
ENAME NVL(TO_CHAR(MGR),'NOMANAGER')
---------- -----------------------------
KING No Manager
Books
1. Modern Database Management (Sixth Edition) by Fred R. McFadden, Jeffrey A.
Hoffer, Mary B. Prescott
2. Database System Concepts (Fifth Edition) by Henry F. Korth, S. Sudarshan, A.
Silberschatz
3. Oracle-database-10g-sql-fundamentals-1-student-guide-volume-1
4. SQL and Relational Theory: How to Write Accurate SQL Code by C.J. Date
5. Database Systems: A Practical Approach to Design, Implementation and
Management (4th Edition) by Thomas M. Connolly, Carolyn E. Begg
6. Fundamentals of Database Systems, 5th Edition by RamezElmasri, Shamkant B.
Navathe
7. Database Design and Relational Theory: Normal Forms and All That Jazz by C. J. Date
8. An Introduction to Database Systems 8th Edition, by C.J. Date
References
1. [Link]
2. [Link]
[Link]#GUID-BCCCFF75-D2A4-43AD-8CAF-C3C97D92AC63
3. [Link]
mation
4. [Link]
5. [Link]