Database
SQL
Single-Row Functions
Pascal TUFENKJI 1 Database SQL
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 statement
– Describe the use of conversion functions
Pascal TUFENKJI 2 Database SQL
SQL Functions
Function
Input Output
Function
arg 1 performs action Result
value
arg 2
arg n
Pascal TUFENKJI 3 Database SQL
Two types of SQL Functions
Function
Single-row Multiple-row
functions functions
Pascal TUFENKJI 4 Database SQL
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
• Accept arguments that can be a column or
an expression
function_name (column|expression, [arg1, agr2, …])
Pascal TUFENKJI 5 Database SQL
Single-Row Functions
Character
General Number
Single-row
functions
Conversion Date
Pascal TUFENKJI 6 Database SQL
Character Functions
Character
functions
Case conversion Character manipulation
functions functions
LOWER CONCAT
UPPER SUBSTR
INITCAP LENGTH
INSTR
LPAD | RPAD
Pascal TUFENKJI 7 Database SQL
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
Pascal TUFENKJI 8 Database SQL
Using Case Conversion Functions
SELECT 'The job title for '||INITCAP(ename)||
' is '||LOWER(job) AS "EMPLOYEE DETAILS"
FROM emp;
Pascal TUFENKJI 9 Database SQL
Using Case Conversion Functions
Display the employee number, name and
department number for employee Blake.
SELECT empno, ename, deptno FROM emp
WHERE ename = 'blake';
SELECT empno, ename, deptno FROM emp
WHERE LOWER(ename) = 'blake';
Pascal TUFENKJI 10 Database SQL
Character Manipulation Functions
Function Result
TRIM(' Good String ') Good String
CONCAT('Good','String') GoodString
SUBSTR('String',1,3) Str
LENGTH('String') 6
REPLACE('String','t','p') Spring
LPAD('1234',6,'0') 001234
STRPOS('String','i') 4
INSTR('String',‘r') 3
CHARINDEX('r','String') 3
Pascal TUFENKJI 11 Database SQL
Using the Character Manipulation Functions
SELECT empno, ename, substr(ename,1,3) as sub
FROM emp;
Pascal TUFENKJI 12 Database SQL
Using the Character Manipulation Functions
SELECT empno, ename, length(ename) as len
FROM emp;
Pascal TUFENKJI 13 Database SQL
Using the Character Manipulation Functions
SELECT empno, ename, job,
replace(ename,'A','I') as newname,
lpad(ename,'10','x') as pad,
strpos(ename,'A') as pos
FROM emp
WHERE substr(job,1,5) = 'SALES';
Pascal TUFENKJI 14 Database SQL
Number Functions
Function Result
ROUND(45.926, 2) 45.93
TRUNC(45.926, 2) 45.92
MOD(9, 4) 1
CEIL(22.12) 23
FLOOR(12.95) 12
FORMAT(12332.123456, 4); 12,332.1235
Pascal TUFENKJI 15 Database SQL
Using the ROUND Functions
SELECT round(45.923,2) as r1,
round(45.923,0) as r2,
round(45.923,-1) as r3;
SELECT round(45.923,2) as r1,
round(45.923,0) as r2,
round(45.923,-1) as r3
FROM DUAL;
DUAL is a dummy table in Oracle that you can use to view
results from functions and calculations.
Pascal TUFENKJI 16 Database SQL
Using the TRUNC Functions
SELECT trunc(45.923,2) as t1,
trunc(45.923,0) as t2,
trunc(45.923,-1) as t3;
Pascal TUFENKJI 17 Database SQL
Using the MOD Functions
Calculate the remainder of the ratio of
salary to commission for all employees
whose job title is salesman.
SELECT ename, sal, comm, MOD(sal,comm)
FROM emp
WHERE comm > 0;
Pascal TUFENKJI 18 Database SQL
Arithmetic Functions
Description Function Result
Absolute value Abs(-12) 12
Natural logarithm Ln(9) 2.197224
Exponential Exp(2) 7.389056
Power Power(2,8) 256
Square root Sqrt(4) 2
Pascal TUFENKJI 19 Database SQL
Using the Arithmetic Functions
select empno,ename,sal,
floor(sal+power((sal*0.01),2)) as raise
FROM emp
ORDER BY sal desc;
Pascal TUFENKJI 20 Database SQL
Working with Dates
• Every database stores dates in an internal
format: century, year, month, day, hours,
minutes and seconds.
• The default date format differs from one
database to another.
Database Date format
PostgreSQL & MySQL & SQLite YYYY-MM-DD
Oracle DD-MON-YY
SQL Server & MS Access
MM/DD/YYYY
(depending on the Regional Options)
Pascal TUFENKJI 21 Database SQL
Working with Dates
• SYSDATE is a function returning date and time in
Oracle. (DUAL is a dummy table used to view Sysdate)
SELECT SYSDATE FROM DUAL;
• NOW() is a function returning date and time in
PostgreSQL and MySQL
SELECT now();
• GETDATE() is a function returning date and time in
SQL Server (DATE() in MS Acess).
SELECT empno,hiredate,getdate() today FROM emp;
Pascal TUFENKJI 22 Database SQL
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.
Pascal TUFENKJI 23 Database SQL
Oracle Date Functions
Function Description
Number of months
MONTHS_BETWEEN
between two dates
Add calendar months to
ADD_MONTHS
date
Next day of the date
NEXT_DAY
specified
LAST_DAY Last day of the month
ROUND Round date
TRUNC Truncate date
Pascal TUFENKJI 24 Database SQL
Using Oracle Date Functions
Function Result
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
SELECT empno, hiredate,
MONTHS_BETWEEN(SYSDATE,hiredate) AS TENURE,
ADD_MONTHS(hiredate, 6) AS REVIEW,
NEXT_DAY(hiredate,'FRIDAY'), LAST_DAY(hiredate)
FROM emp
WHERE MONTHS_BETWEEN(SYSDATE, hiredate) < 200;
EMPNO | HIREDATE | TENURE | REVIEW | NEXT_DAY | LAST_DAY
------+-----------+--------+-----------+-----------+----------
7839 | 17-NOV-81 | 192.24 | 17-MAY-82 | 20-NOV-81 | 30-NOV-81
7698 | 01-MAY-81 | 198.76 | 01-NOV-81 | 08-MAY-81 | 31-MAY-81
Pascal TUFENKJI 25 Database SQL
Using Oracle Date Functions
Function Result
ROUND(SYSDATE,'MONTH') 01-AUG-03
ROUND(SYSDATE,'YEAR') 01-JAN-04
TRUNC(SYSDATE,'MONTH') 01-JUL-03
TRUNC(SYSDATE,'YEAR') 01-JAN-03
SELECT empno, hiredate,
ROUND(hiredate, 'MONTH'),
TRUNC(hiredate, 'MONTH')
FROM emp
WHERE hiredate LIKE '%82';
EMPNO | HIREDATE | ROUND | TRUNC
------+-----------+-----------+----------
7839 | 09-DEC-82 | 01-DEC-82 | 01-DEC-82
7698 | 23-JAN-82 | 01-FEB-82 | 01-JAN-82
Pascal TUFENKJI 26 Database SQL
PostgreSQL Date Functions
Function Description
NOW Returns the current date
AGE Returns the years and months
EXTRACT Extracts a field from a date
SELECT now() as today;
Pascal TUFENKJI 27 Database SQL
Using Arithmetic Operators with Dates
SELECT ename, now()-hiredate as days
FROM emp
WHERE deptno = 10;
Pascal TUFENKJI 28 Database SQL
Using PostgreSQL Date Functions
SELECT ename, hiredate,
age(hiredate) as tenure,
extract(year from hiredate) as year
FROM emp
WHERE deptno = 10;
Pascal TUFENKJI 29 Database SQL
Working with Intervals in Postgres
• Interval values can be written with the
following syntax:
[+] quantity unit
– quantity is a number
– unit is microsecond, millisecond, second,
minute, hour, day, week, month, year,
decade, century…
Pascal TUFENKJI 30 Database SQL
Working with Intervals in Postgres
SELECT ename, hiredate,
hiredate + interval '10 days' as interv1,
hiredate + interval '2 months' as interv2,
hiredate + interval '1 year' as interv3
FROM emp;
Pascal TUFENKJI 31 Database SQL
MySQL Date Functions
Function Description
NOW() Returns the current date and time
CURDATE() Returns the current date
CURTIME() Returns the current time
DATE() Extracts the date part of a date or date/time expression
EXTRACT() Returns a single part of a date/time
DATE_ADD() Adds a specified time interval to a date
DATE_SUB() Subtracts a specified time interval from a date
DATEDIFF() Returns the number of days between two dates
DATE_FORMAT() Displays date/time data in different formats
PERIOD_DIFF() Returns the number of months between periods
Pascal TUFENKJI 32 Database SQL
Using MySQL Date Functions
SELECT ename, hiredate,
date_format(hiredate,'%d/%m/%Y') as "Date"
FROM emp
WHERE deptno = 10;
Pascal TUFENKJI 33 Database SQL
Working with Intervals in MySQL
SELECT ename, hiredate,
hiredate + interval 10 day as interv1,
hiredate + interval 2 month as interv2,
hiredate + interval 1 year as interv3
FROM emp;
Pascal TUFENKJI 34 Database SQL
SQL Server Date Functions
Function Description
GETDATE() Returns the current date and time
DATEPART() Returns a single part of a date/time
DATEADD() Adds or subtracts a specified time interval from a date
DATEDIFF() Returns the time between two dates
CONVERT() Displays date/time data in different formats
Returns a value formatted with the specified format and
FORMAT()
optional culture in SQL Server
Pascal TUFENKJI 35 Database SQL
Using SQL Server Date Functions
SELECT
ename,
convert(varchar(10),hiredate,103) Hiredate,
format(getdate(),'dd/MM/yyyy hh:mm:ss','en-us') Today,
format(sal * 1.1,'#,###.##') "New Salary",
convert(varchar,cast(sal as money),1) "Old Salary"
FROM emp
WHERE deptno = 10;
Pascal TUFENKJI 36 Database SQL
Data type casting
• The key use of CAST is to deal with
data types. It converts from one data
type to another.
CAST ('string' AS type)
Pascal TUFENKJI 37 Database SQL
Using Casting
SELECT now() as today;
SELECT cast(now() as date) as date;
SELECT cast(now() as time) as time;
Pascal TUFENKJI 38 Database SQL
Conversion Functions
Datatype
conversion
Implicit datatype Explicit datatype
conversion conversion
Pascal TUFENKJI 39 Database SQL
Implicit Datatype Conversion
• For assignments, the Oracle server can
automatically convert the following.
From To
VARCHAR2 or CHAR NUMBER
VARCHAR2 or CHAR DATE
NUMBER VARCHAR2
DATE VARCHAR2
Note:
- CHAR to NUMBER conversions succeed only if the character string represents a valid number
- CHAR to DATE conversions succeed only if the character string has the default format DD-MON-YY
Pascal TUFENKJI 40 Database SQL
Explicit Datatype Conversion
TO_NUMBER TO_DATE
NUMBER CHARACTER DATE
TO_CHAR TO_CHAR
Pascal TUFENKJI 41 Database SQL
TO_CHAR Function with Dates
TO_CHAR(date, 'format')
• 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.
Pascal TUFENKJI 42 Database SQL
Elements of Date Format Model
Element Description
YYYY Full year in numbers
MM Two-digit value for month
MONTH Full name of the month
Three-letter abbreviation of
DY
the day of the week
DAY Full name of the day
Pascal TUFENKJI 43 Database SQL
Elements of Date Format Model
Element Description
HH12 or HH24 Hour of day (1-12) or (0-23)
MI Minute (0-59)
SS Seconds (0-59)
Pascal TUFENKJI 44 Database SQL
Using TO_CHAR Function with Dates
SELECT ename,
TO_CHAR(hiredate,'DD Month YYYY') as hiredate
FROM emp;
Pascal TUFENKJI 45 Database SQL
Using TO_CHAR Function with Dates
SELECT ename,
TO_CHAR(hiredate,'fmDD Month YYYY') as hiredate
FROM emp;
Pascal TUFENKJI 46 Database SQL
Using DATE_FORMAT Function in MySQL
SELECT ename,
DATE_FORMAT(hiredate,'%d %M %Y') as hiredate
FROM emp;
Pascal TUFENKJI 47 Database SQL
TO_CHAR Function with Numbers
TO_CHAR(number, 'format')
Use these formats with TO_CHAR function to
display a number value as a character
9 Represents a number
0 Forces a zero to be displayed
$ Places a floating dollar sign
. Prints a decimal point
, Prints a thousand indicator
Pascal TUFENKJI 48 Database SQL
Using TO_CHAR Function with Numbers
SELECT ename,
TO_CHAR(sal,'9,999 $') as salary
FROM emp;
Pascal TUFENKJI 49 Database SQL
Using FORMAT Function in MySQL
SELECT ename,
FORMAT(sal,0) as salary
FROM emp;
Pascal TUFENKJI 50 Database SQL
TO_NUMBER and TO_DATE Functions
• Convert a character string to a number
format using the TO_NUMBER function
TO_NUMBER(char, 'format')
• Convert a character string to a date
format using the TO_DATE function
TO_DATE(char, 'format')
Pascal TUFENKJI 51 Database SQL
Using the TO_DATE Function
SELECT ename, hiredate
FROM emp
WHERE hiredate =
TO_DATE('Feb 22, 1981','Mon DD, YYYY');
Pascal TUFENKJI 52 Database SQL
Using the STR_TO_DATE Function in MySQL
SELECT ename, hiredate
FROM emp
WHERE hiredate =
STR_TO_DATE('Feb 22, 1981','%b %d, %Y');
Pascal TUFENKJI 53 Database SQL
Using the TO_NUMBER Function
SELECT matricule,
to_number(matricule,999999) + 2 as matricule2
FROM etudiant
limit 2;
Pascal TUFENKJI 54 Database SQL
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
Pascal TUFENKJI 55 Database SQL
Nesting Functions
SELECT empno,ename,job,
rpad(initcap(trim(
' M. '||ename||lower(' IS A ')||job
)),30,'.')
FROM emp
ORDER BY job, ename;
Pascal TUFENKJI 56 Database SQL
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
• Alter date formats for display using
functions
• Convert column data types using functions
Pascal TUFENKJI 57 Database SQL
