SQL FUNCTIONS

By
Neil A. Basabe

1
 SQL Functions
• SQL Functions
– SQL functions are built-in commands that perform
predefined and specific tasks.
• 2 Major Types of SQL Functions:
1. Scalar Functions
2. Arithmetic Functions

2
 Scalar Functions
• Scalar functions are used to manipulate
columns or expressions.
• Categories of Scalar Functions:
1. Character/String Functions
2. Date/Time Functions
3. Math Functions

3
 Character/String Functions
1. COALESCE 9. LENGTH
2. CONCAT or || 10. LOCATE
3. SUBSTR 11. CHAR
4. NULLIF 12. CAST
5. RIGHT 13. REPLACE
6. LEFT 14. REPEAT
7. UCASE 15. TRANSLATE
8. LCASE

4
 COALESCE
• Substitution of NULL Values
• The COALESCE scalar function allows multiple
parameters.
• The function takes a variable number of parameters
and returns the value of the first parameter that is not
null. The result is null if the values of all parameters
are null.
• Every expression must be of a compatible data type (if
the first expression is numeric, every expression must
be numeric, and so on.)
5
SELECT DEPTNAME,
COALESCE(MGRNO,'UNKNOWN') AS MANAGER
FROM DEPARTMENT
ORDER BY DEPTNAME;

6
 Arithmetic with NULL Values
SELECT EMPNO, SALARY, COMM,
SALARY + COALESCE(COMM,0) AS TOTALINCOME
FROM EMPLOYEE;

7
 CONCAT or ||
• returns a concatenated string values (multiple
string values combined as one string value)
SELECT col-1 CONCAT col-2 AS
"generated columns
FROM table-name
WHERE . . .

8
 EXAMPLES
1. SELECT deptno, deptname CONCAT ' ' CONCAT
mgrno AS "DEPT-MGR“
FROM DEPARTMENT

2. SELECT empno, firstnme || ' ' || midinit || '. '

9
 Concatenation of Values
SELECT LASTNAME CONCAT ',' CONCAT
FIRSTNME AS NAME
FROM EMPLOYEE
WHERE WORKDEPT='A00'
ORDER BY NAME;

CONCAT combines character

10
 SUBSTR
• returns part of the entire string value
Syntax:
SELECT col-1, col-2, . . .SUBSTR(string-column,
begin-char, no.-chars) AS "generated column“
FROM table-name
WHERE . . .

11
 Substring of Strings
SELECT SUBSTR(PROJNO,1,2) AS PROJ_CLASS,
PROJNAME
FROM PROJECT
WHERE PROJNO LIKE 'IF%';
Note: SUBSTR(PROJNO,1,2) extracts the first two
characters of PROJNO. The function can be interpreted to
read: Within the PROJNO column, start with the first
character and return the two characters.

12
 NULLIF
• compares two values and returns a NULL as the
result if the two values are equal, or returns the
first value as the result when they are not equal.
Syntax:
SELECT col-1, col-2, . . .
NULLIF(column-1, column-2)
FROM table-name
WHERE . . .

13
 EXAMPLE
SELECT empno, lastname,
NULLIF(job, 'MANAGER') AS
POSITION, salary
FROM EMPLOYEE
FETCH FIRST 20 ROWS ONLY

14
OUTPUT

15
 LENGTH
• returns the length (number of characters) of a
string value.
Syntax:
SELECT col-1, col-2, . . .
LENGTH(string-column)
FROM table-name
WHERE . . .

16
 EXAMPLE
SELECT deptno, deptname,
LENGTH(deptname) AS "No. OF
CHARS."
FROM DEPARTMENT

17
OUTPUT

18
 LEFT
• returns a sequence of characters starting from
the leftmost character.
Syntax:
SELECT col-1, col-2, . . .
LEFT(string-column, no.-chars)
FROM table-name
WHERE . . .

19
 EXAMPLE
SELECT empno, lastname,
LEFT(lastname, 4) AS "FIRST 4 CHARS
OF LASTNAME"
FROM EMPLOYEE

20
OUTPUT

21
 RIGHT
• returns a sequence of characters starting from
the rightmost character
Syntax:
SELECT col-1, col-2, . . .
RIGHT(string-column, no.-chars)
FROM table-name
WHERE . . .

22
 EXAMPLE
SELECT empno, lastname,
RIGHT(lastname, 4) AS "LAST 4 CHARS
OF LASTNAME"
FROM EMPLOYEE

23
OUTPUT

24
 UCASE
• converts all characters of the string value into
capital letters.

Syntax:
SELECT col-1, col-2, . . .
UCASE(string-column)
FROM table-name
WHERE . . .
25
 EXAMPLE
SELECT projno, projname,
UCASE(projname) AS "PROJNAME IN
CAPS"
FROM PROJECT

26
OUTPUT

27
 LCASE
• converts all characters of the string value into small letters.
Syntax:
SELECT col-1, col-2, . . .
LCASE(string-column)
FROM table-name
WHERE . . .

Example:
SELECT projno, projname, LCASE(projname) AS "PROJNAME IN
SMALLS"
FROM PROJECT

28
OUTPUT

29
 CAST
• converts the data type of a column into a new
data type.
Syntax:
SELECT col-1, col-2, . . .CAST(column-name AS
new data-type)
FROM table-name
WHERE . . .

30
 EXAMPLES
1. SELECT empno, lastname, salary,
CAST(salary AS INT) AS "INTEGERIZED SALARY"
FROM EMPLOYEE
2. SELECT CAST(empno AS DECIMAL(10,2)) AS
EMPNO, lastname, salary
FROM EMPLOYEE

31
OUTPUT FROM # 1

32
OUTPUT FROM # 2

33
 CHAR
• returns a fixed-length character string
representation of the column value.
Syntax:
SELECT col-1, col-2, . . .
CHAR(column-name, no.-chars)
FROM table-name
WHERE . . .

34
 EXAMPLES
1. SELECT empno, lastname, salary,
CHAR(lastname, 3) AS "3-LETTER LASTNAME“
FROM EMPLOYEE

2. SELECT empno, lastname, CHAR(job, 4) AS

35
OUTPUT FROM # 1

36
OUTPUT FROM # 2

37
 LOCATE
• returns the starting position of the first occurrence of
the search string within the source string.
• If the optional start position is specified, it indicates that
the character position in the source string at which the
search is to begin.
Syntax:
SELECT col-1, col-2, . . .LOCATE(search-string, source-
string [,start-pos])
FROM table-name
WHERE . . .
38
 EXAMPLE
SELECT empno, lastname, job, LOCATE('ER', JOB)
AS "ER POSITION"
FROM EMPLOYEE

39
OUTPUT

40
 REPLACE
• replaces all occurrences of expression-1 with
expression-2 of the string column.
Syntax:
SELECT col-1, col-2, . . . REPLACE(column-name,
expression-1, expression-2)
FROM table-name
WHERE . . .

41
 EXAMPLE
SELECT empno, lastname, job,
REPLACE(JOB, 'E', 'IS') AS "NEW JOB“
FROM EMPLOYEE

42
OUTPUT

43
 REPEAT
• returns the character string composed of the
argument repeated a number of times specified by
the user.
Syntax:
SELECT col-1, col-2, . . .REPEAT(argument, N)
FROM table-name
WHERE . . .
where: N is the number of times the argument is
repeated.
44
 EXAMPLE
SELECT empno, lastname, job,
REPEAT(JOB, 2) AS "NEW JOB“
FROM EMPLOYEE

45
OUTPUT

46
 TRANSLATE
• returns a value in which one or more characters
of a string value is translated into character
defined by the user.
Syntax:
SELECT col-1, col-2, . . .
TRANSLATE(string-column, translate this, with this)
FROM table-name
WHERE . . .

47
 EXAMPLE
SELECT empno, lastname, job,
TRANSLATE(JOB, 'O', 'A') AS "NEW JOB"
FROM EMPLOYEE

48
OUTPUT

49
 Date/Time Functions
1. DATE 7. YEAR
2. WEEK 8. DAYOFYEAR
3. DAY 9. MONTHNAME
4. TIME 10. MONTH
5. DAYS 11. DAYOFWEEK
6. DAYNAME

50
 DATE/TIME Scalar Functions
• DAY, MONTH, WEEK, YEAR, HOUR, MINUTE,
SECOND, MICROSECOND, DATE, TIME
– Extract portions of a date, time, timestamp or
duration

51
 YEAR
YEAR - returns the year part of a date by extracting it from a
date value.
Syntax:
SELECT col-1, col-2, . . .
YEAR(DATE-Argument)
FROM table-name
WHERE . . .
Example:
SELECT empno, lastname, job,
YEAR(HIREDATE) AS "YEAR HIRED"
FROM EMPLOYEE

52
 MONTH
MONTH - returns the numeric month value of a date by extracting
it from a date value.
Syntax:
SELECT col-1, col-2, . . .
MONTH(DATE-Argument)
FROM table-name
WHERE . . .
Example:
SELECT empno, lastname, job, hiredate,
MONTH(HIREDATE) AS "MONTH HIRED OF A YEAR"
FROM EMPLOYEE

53
 DAY
DAY - returns the numeric day value of a date by extracting it from a
date value.
Syntax:
SELECT col-1, col-2, . . .
DAY(DATE-Argument)
FROM table-name
WHERE . . .
Example:
SELECT empno, lastname, job, hiredate,
DAY(HIREDATE) AS "DAYth HIRED OF A YEAR"
FROM EMPLOYEE

54
 DATE Scalar Functions
SELECT LASTNAME, FIRSTNME,
CURRENT_DATE - BIRTHDATE AS AGE,
YEAR(CURRENT_DATE - BIRTHDATE) AS YEARS,
MONTH(CURRENT_DATE - BIRTHDATE) AS MONTHS,
DAY(CURRENT_DATE - BIRTHDATE) AS DAYS
FROM EMPLOYEE
WHERE YEAR(CURRENT_DATE-BIRTHDATE)>45
ORDER BY AGE DESC, LASTNAME;

55
 WEEK
WEEK - returns the numeric week value of the year as an integer
value ranging from 1 - 54. The week count starts from Sunday.
Syntax:
SELECT col-1, col-2, . . .
WEEK(DATE-Argument)
FROM table-name
WHERE . . .
Example:
SELECT empno, lastname, job, hiredate,
WEEK(HIREDATE) AS "WEEKth HIRED OF A YEAR"
FROM EMPLOYEE

56
OUTPUT

57
 DAYS
DAYS - returns an integer representation of the numbers of days since
Jan 1, 0001 up to the date specified.
Syntax:
SELECT col-1, col-2, . . .
DAYS(DATE-Argument)
FROM table-name
WHERE . . .

Example:
SELECT empno, lastname, job, hiredate,
DAYS(HIREDATE) AS "No. of Days since 01-01-0001"
FROM EMPLOYEE

58
OUTPUT

59
 DAYNAME
DAYNAME - returns a mixed case character string containing the name
of the day (for example, Friday) for the day portion of
the date argument based on the location when the database
was started.
Syntax:
SELECT col-1, col-2, . . .
DAYNAME(DATE-Argument)
FROM table-name
WHERE . . .
Example:
SELECT empno, lastname, job, hiredate,
DAYNAME(HIREDATE) AS "Name of Day Hired"
FROM EMPLOYEE

60
OUTPUT

61
 MONTHNAME
MONTHNAME - returns a mixed case character string containing the
name of the month (for example, November) for the
month portion of the date argument based on the location
when the database was started.
Syntax:
SELECT col-1, col-2, . . .
MONTHNAME(DATE-Argument)
FROM table-name
WHERE . . .

Example:
SELECT empno, lastname, job, hiredate,
MONTHNAME(HIREDATE) AS "Name of Month Hired"
FROM EMPLOYEE

62
OUTPUT

63
 DAYOFWEEK
DAYOFWEEK - returns an integer value representing the number of
day in a week. The value ranges from 1 to 7. 1 being Sunday.
Syntax:
SELECT col-1, col-2, . . .
DAYOFWEEK(DATE-Argument)
FROM table-name
WHERE . . .
Example:
SELECT empno, lastname, job, hiredate,
DAYOFWEEK(HIREDATE) AS "WEEKth hired"
FROM EMPLOYEE

64
OUTPUT

65
 DAYOFYEAR
DAYOFYEAR - returns an integer value representing the number of
day in a year. The value ranging from 1 to 366.
Syntax:
SELECT col-1, col-2, . . .
DAYOFYEAR(DATE-Argument)
FROM table-name
WHERE . . .
Example:
SELECT empno, lastname, job, hiredate,
DAYOFYEAR(HIREDATE) AS "Nth days hired"
FROM EMPLOYEE

66
DAYOFYEAR

67
 Math Functions
1. DECIMAL
2. SIGN
3. POWER
4. CEIL
5. SQRT
6. FLOOR
7. ROUND

68
DECIMAL - returns the decimal representation of a number.
Syntax:
SELECT col-1, col-2, . . .DECIMAL(argument, no. of digits, no. of
decimal places)
FROM table-name
WHERE . . .

Example:
1. SELECT empno, lastname, job, hiredate,
DECIMAL(salary * 1.10, 12, 2) AS "INC-SALARY"
FROM EMPLOYEE

69
 EXAMPLE # 2
SELECT EMPNO, SALARY,
DECIMAL(SALARY * 1.0375,8,2) AS NEWSALARY
FROM EMPLOYEE
WHERE SALARY<20000
ORDER BY EMPNO;

70
 SIGN
SIGN - returns an indicator of the sign of the argument.
-1 is returned when the argument is less than zero.
0 is returned when the argument is zero.
1 is returned when the argument is greater than zero.

Syntax:
SELECT col-1, col-2, . . .
SIGN(argument)
FROM table-name
WHERE . . .

71
 EXAMPLES
1. SELECT empno, lastname, job, hiredate,
SIGN(bonus-salary) AS "INDICATOR“
FROM EMPLOYEE
2. SELECT empno, lastname, job, hiredate,
SIGN(bonus-bonus) AS "INDICATOR“
FROM EMPLOYEE
3. SELECT empno, lastname, job, hiredate,
SIGN(bonus+salary) AS "INDICATOR"
FROM EMPLOYEE
72
OUTPUT FROM # 1

73
 POWER
POWER - returns a value of expression-1 raise to the power
of expression-2.
Syntax:
SELECT col-1, col-2, . . .
POWER(expression-1, expression-2)
FROM table-name
WHERE . . .
Example:
SELECT empno, lastname, job,
POWER(bonus,2) AS "BONUS SQUARED"
FROM EMPLOYEE

74
OUTPUT

75
SQRT - returns the square root of the given expression.
Syntax:
SELECT col-1, col-2, . . .
SQRT(expression)
FROM table-name
WHERE . . .
Examples:
SELECT empno, lastname, job,
SQRT(bonus+comm) AS "SQRT OF BONCOM”
FROM EMPLOYEE
76
OUTPUT

77
 ROUND
ROUND - returns expression-1 rounded to the 1, 2, 3, ... places to
the right of the decimal point if the round-places is
positive, or to the left of the decimal point if the round- places
is zero or negative.
Syntax:
SELECT col-1, col-2, . . .
ROUND(expression, round-place)
FROM table-name
WHERE . . .

78
 EXAMPLE
SELECT empno, lastname, job,
ROUND(bonus + bonus * 1.0587) AS
"ROUNDED BONCOM"
FROM EMPLOYEE

79
OUTPUT

80
Laboratory Exercises – SQL Functions

81