COMSATS UNIVERSITY ISLAMABAD

WAH CAMPUS
06
Part-1

SQL Built-in Functions

Single Row Character Functions

Instructor: Mr. Amjad Usman Course Code: CSC371

Lecturer Course Title: Database Systems 1
Dept. of Computer Science Credit Hours: 4 (3+1)
Outline

Today, you will learn:

 What is an SQL function?
 Single row functions
 Character functions
 Number functions
 Date functions
 Conversion functions
 General functions
 Multi rows functions
 Group functions (Max, Min, Sum, Count, Avg, etc.)

2 Prepared By: Amjad Usman (CUI, Wah)

SQL Functions
A function is a program to perform a specific operation
It accepts input parameters and returns a single value.
A function returns only one value per execution.
Input Output
Function

arg 1 Function
performs action
arg 2
Result
value

arg n
3 Prepared By: Amjad Usman (CUI, Wah)
Two Types of SQL Functions

Functions

Single-row Multiple-row
functions functions

A single row function takes A multi row function takes

single row/record as input multiple rows/records as input
to perform operation. to perform operation.

4 Prepared By: Amjad Usman (CUI, Wah)

Single-Row 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 data type
 Can be nested
 Accept arguments which can be a column or an expression

function_name [(arg1, arg2,...)]

5 Prepared By: Amjad Usman (CUI, Wah)

Single-Row Functions

Character

General Number
Single-row
functions

Conversion Date

6 Prepared By: Amjad Usman (CUI, Wah)

Character Functions

Character
functions

Case-conversion Character-manipulation
functions functions
LOWER CONCAT
UPPER SUBSTR
INITCAP LENGTH
INSTR
LPAD | RPAD
TRIM
REPLACE

7 Prepared By: Amjad Usman (CUI, Wah)

Case Conversion Functions

 LOWER() function
 converts a given character column, literal, or expression into
lowercase i.e. small letters.
 UPPER() function
 converts a given character column, literal, or expression into
uppercase, i.e. capital letters.
 INITCAP() function
 converts a given character column, literal, or expression into initial
case i.e. first letter of each word is capital.

8 Prepared By: Amjad Usman (CUI, Wah)

Case Conversion Functions

These functions convert case for character strings.

Function Result
LOWER('SQL Course') sql course
UPPER(‘sql course') SQL COURSE
INITCAP(‘sql course') Sql Course

9 Prepared By: Amjad Usman (CUI, Wah)

Example

select lower(first_name),
upper(last_name),
initcap(job_id)
from employees;

10 Prepared By: Amjad Usman (CUI, Wah)

Using Case Conversion Functions

Display the employee number, name, and department number for employee Higgins:

SELECT employee_id, last_name, department_id

FROM employees
WHERE last_name = 'higgins';
no rows selected

SELECT employee_id, last_name, department_id

FROM employees
WHERE LOWER(last_name) = 'higgins';

11 Prepared By: Amjad Usman (CUI, Wah)

Length and concat Functions

The LENGTH(string) function uses a character string as an input parameter

and returns a numeric value representing the number of characters present
in that string.
length('A short string’) → 14

The CONCAT(string 1, string 2) function takes two strings and

concatenates or joins them in the same way that the concatenation operator
|| does:

concat('SQL is',' easy to learn.’) → SQL is easy to learn.

12 Prepared By: Amjad Usman (CUI, Wah)
The Length Function

SQL> SELECT name, LENGTH(name), stadr, LENGTH(stadr), city, LENGTH(city)

FROM donor;

NAME LENGTH(NAME) STADR LENGTH(STADR) CITY LENGTH(CITY)

--------------- ------------ --------------- ------------- ---------- ------------
Stephen Daniels 15 123 Elm St 10 Seekonk 7
Jennifer Ames 13 24 Benefit St 13 Providence 10
Carl Hersey 11 24 Benefit St 13 Providence 10
Susan Ash 9 21 Main St 10 Fall River 10
Nancy Taylor 12 26 Oak St 9 Fall River 10
Robert Brooks 13 36 Pine St 10 Fall River 10

LENGTH tells the length of the characters entered into the column/field.
NOTE: Embedded spaces are counted.

13 Prepared By: Amjad Usman (CUI, Wah)

The substr function
The SUBSTR function accepts three parameters and returns a string
consisting of the number of characters extracted from the source string,
beginning at the specified start position:

SUBSTR(string, start position, number of characters)

substr('http://www.domain.com’, 12, 6) → domain

In case, you omit the last parameter i.e. No. of characters then it will extract
all characters till end.

substr('http://www.domain.com’, 12) → domain.com

14 Prepared By: Amjad Usman (CUI, Wah)

 The substr function
SQL> SELECT SUBSTR(datefst,4,3), datefst
FROM donor;
length of
SUB DATEFST name of substring
--- --------- column/field SQL> SELECT SUBSTR(datefst,4)
JUL 03-JUL-98 position of first FROM donor;
MAY 24-MAY-97 character of
JAN 03-JAN-98 substring SUBSTR(DATEFST,4)
MAR 04-MAR-92 -----------------------------
MAR 04-MAR-92 JUL-98
APR 04-APR-98 MAY-97
JAN-98
MAR-92
MAR-92
APR-98

15 Prepared By: Amjad Usman (CUI, Wah)

The instring Functions
The INSTR function returns a number that represents the position in the
source string, beginning from the given start position, where the nth
occurrence of the search item begins:

INSTR(source string, search item, [start position], [nth occurrence of search item])

instr(‘comsats university’, ‘s’, 1, 1) → 4

instr(‘comsats university’, ‘s’) → 4
instr(‘comsats university’, ‘s’, 1, 2) → 7

16 Prepared By: Amjad Usman (CUI, Wah)

 Example
SQL> SELECT * FROM donor;
IDNO NAME STADR CITY ST ZIP DATEFST YRGOAL CONTACT
----- --------------- --------------- ---------- -- ----- --------- --------- ------------
11111 Stephen Daniels 123 Elm St Seekonk MA 02345 03-JUL-98 500 John Smith
12121 Jennifer Ames 24 Benefit St Providence RI 02045 24-MAY-97 400 Susan Jones
22222 Carl Hersey 24 Benefit St Providence RI 02045 03-JAN-98 Susan Jones
23456 Susan Ash 21 Main St Fall River MA 02720 04-MAR-92 100 Amy Costa
33333 Nancy Taylor 26 Oak St Fall River MA 02720 04-MAR-92 50 John Adams
34567 Robert Brooks 36 Pine St Fall River MA 02720 04-APR-98 50 Amy Costa

SQL> SELECT datefst, INSTR(datefst,'A') character being

2 FROM donor; looked for
column/field being
examined
DATEFST INSTR(DATEFST,'A')
--------- ------------------
03-JUL-98 0 No A in JUL
24-MAY-97 5 A in 5th character position in MAY
03-JAN-98 5 A in 5th character position in JAN
04-MAR-92 5 A in 5th character position in MAR
A in 5th character position in MAR
04-MAR-92 5
A in 4th character position in APR
04-APR-98 4
17 Prepared By: Amjad Usman (CUI, Wah)
The LPAD and RPAD functions
 The LPAD (Left Pad) function returns a string padded with a specified number
of characters to the left of the source string.
 The RPAD (Right Pad) function returns a string padded with a specified
number of characters to the right of the source string.
 The character strings used for padding include:
 character literals, column values, or expressions.
 Blank spaces, tabs, and special characters may be used as padding characters.
 Numeric and date literals are implicitly cast as characters.
 Numeric or date expressions are evaluated before being converted to strings
destined for padding.

18 Prepared By: Amjad Usman (CUI, Wah)

The LPAD and RPAD functions

Syntax
LPAD(string, length, [padstring])
Where:
String → source string
Length → an integer value that represents the final length of the
string returned (like field size)
Padstring → the character string to be used as padding
LPAD(salary 10 ‘*’)

19 Prepared By: Amjad Usman (CUI, Wah)

The LPAD and RPAD functions
SQL> SELECT RPAD(name,20,’-’) as ‘Name’,
LPAD(salary,9,'*’) as ‘Salary’,
LPAD(bonus,5,'$’) as ‘Bonus’
FROM first_pay;

Name Salary Bonus

-------------------- --------- -----
Linda Costa--------- ****45000 $1000
John Davidson------- ****40000 $1500
Susan Ash----------- ****25000 $$500
Stephen York-------- ****42000 $2000
Richard Jones------- ****50000 $2000
Joanne Brown-------- ****48000 $2000
20 Prepared By: Amjad Usman (CUI, Wah)
The Replace function
 The REPLACE function replaces all occurrences of a search item in a source
string with a replacement term and returns the modified source string.
 If the length of the replacement term is different from that of the search item,
then the lengths of the returned and source strings will be different.
 If the search string is not found, the source string is returned unchanged.
 Numeric and date literals and expressions are evaluated before being
implicitly cast as characters when they occur as parameters to the REPLACE
function.

21 Prepared By: Amjad Usman (CUI, Wah)

The Replace function Syntax

REPLACE(source string, search item, [replacement term])

Description:
source string → it is the string where you do replacements
search item → it is the string you want to replace/change
replacement term→ it is the new string you want to replace

 If the replacement term parameter is omitted, each occurrence

of the search item is removed from the source string.
 In other words, the search item is replaced by an empty string

22 Prepared By: Amjad Usman (CUI, Wah)

The Replace function Syntax
SELECT jobcode, REPLACE(jobcode,'CI','IT')
FROM first_pay;
JO REPL
-- ----
CI IT
IN IN
AP AP
CM CM
CI IT
IN IN
23 Prepared By: Amjad Usman (CUI, Wah)
The Trim function

 The TRIM function removes characters from the beginning or

end of character literals, columns or expressions.
 Numeric and date literals are automatically cast as characters
when they occur as parameters to the TRIM function.
 Numeric or date expressions are evaluated first before being
converted to strings ready to be trimmed.

24 Prepared By: Amjad Usman (CUI, Wah)

The Trim Function

TRIM( [leading | trailing | both] trimstring from sourceString )

Description:
 Leading → from start
 Trailing → from end
 Both → from start and end
 Trimstring → the string you want to trim (remove)
 SourceString → it is the main string where you want to perform trimming
 NOTE:
 Leading, trailing and both are directions.

25 Prepared By: Amjad Usman (CUI, Wah)

The Rules of Trimming
 TRIM(s) removes spaces from both sides of the input string. When
no direction of trimming is specified, then spaces are trimmed from
both sides of the string.

 TRIM(trailing trimstring from s) removes all occurrences of

trimstring from the end of the string s if it is present.

 TRIM(leading trimstring from s) removes all occurrences of

trimstring from the beginning of the string s if it is present.

 TRIM(both trimstring from s) removes all occurrences of trimstring

from the beginning and end of the string s if it is present.
26 Prepared By: Amjad Usman (CUI, Wah)
The Trim Function

SELECT trim( TRAILING ‘e’ FROM 1+2.14 || ' is pie’ )

FROM dual; ‘3.14 is pi’

SELECT trim( BOTH '*’ FROM '*******Hidden*******’ ) ‘Hidden’

FROM dual;

SELECT trim(‘D’ from ‘Daud Ahmad’) ‘aud Ahmad’

FROM dual;

27 Prepared By: Amjad Usman (CUI, Wah)

Character-Manipulation Functions Summary
Function Result
LENGTH('HelloWorld') 10
CONCAT('Hello', 'World') HelloWorld
SUBSTR('HelloWorld',1,5) Hello
INSTR('HelloWorld', 'W') 6
LPAD(salary,10,'*') *****24000
RPAD(salary, 10, '*') 24000*****
TRIM('H' FROM 'HelloWorld') elloWorld
TRIM(‘ HelloWorld ') HelloWorld
REPLACE(‘Good Night’,’Night’,’Bye’) Good Bye
28 Prepared By: Amjad Usman (CUI, Wah)
Challenge: Write output

Select instr(‘comsats university’, ‘s’, -1, 1) from dual;

Select substr(‘comsats university’, -1, 3) from dual;
Select replace(10000-3,'9','85') FROM dual;
Select replace('1#3#5#7#9#','#') FROM dual;
SELECT trim(1 from sysdate) FROM dual;

29 Prepared By: Amjad Usman (CUI, Wah)

Book Reading
CUI WAH

Chapter No.6 SQL: Data Manipulation

Chapter No.4 Single-Row Functions

Prepared By: Amjad Usman (CUI, Wah) 30
 Next Lecture
CUI WAH

Number and Date Functions

Single Row Functions

Prepared By: Amjad Usman (CUI, Wah) 31

 Thanks for watching.
#stayHome #staySafe

Subscribe our YouTube channel

“Learn with Amjeeko”

Prepared By: Amjad Usman (CUI, Wah) 32