Chapter Nine Data Manipulation Language DML Functions

1
Chapter NineData Manipulation Language
(DML)Functions

• Objectives
• Single Row functions
• Character functions
• Number functions
• Date functions

2
Functions

• Introduction
• Types of functions
• Single row
• Multiple rows

3
Single Row Functions

• FACTS
• Act on each row
• Return one result per row
• May modify the data type
• Can be nested

4
Single Row Functions

• Character
• Number
• Date
• Conversion
• General

5
Character Manipulation

• LOWER(Col Exp)
• LOWER(Database course)
• UPPER (Col Exp)
• UPPER (Database course)
• INITCAP (Col Exp)
• INITCAP (Database course)

6
Character Manipulation

• CONCAT (Col1 Exp1, Col2 Exp2)
• CONCAT(This ,that)
• SUBSTR(Col Exp,n,m)
• SUBSTR(This is it,2,5)
• LPAD(Col Exp,n,string)
• LPAD(name,9,.)

7
Character Manipulation

• LENGTH(Col Exp)
• LENGTH(this is it)
• CHR(integer)
• CHR(97)
• INSTR(Computer,m)

8
List(Characters)

• Example
• SELECT LOWER(name),
• UPPER(major), LENGTH(name)
• FROM student
• SELECT CONCAT(Name , Address) , GPA
• FROM Student
•  
• SELECT Name ---gt Address , GPA
• FROM Student

9
List(Characters)

• SELECT RPAD (Name, 40, .), GPA
• FROM Student
• JOHN............... 3.1
• MARY ............. 3.2
• SELECT RPAD (Name, 20), GPA
• FROM Student
• MARY 3.2
• MOHAMMAD 3.3
• SELECT LPAD (Name, 20), GPA
• FROM Student
• MARY 3.2
• MOHAMMAD 3.3

10
List(Characters)

• RTRIM(Col)
• RTRIM (Name)
• RTRIM (Name, .)
• LTRIM (Name, ABC)
•  
• LTRIM ( RTRIM( Name, . ), A)
• From Student

11
List(Characters)

• SELECT Name
• FROM Student
• WHERE LENGTH(Address)lt20
• SELECT Name, SUBSTR (SSN, 5 ,2)
• FROM Student
• SELECT Name, SUBSTR (SSN,5)
• FROM Student

12
List(Characters)

• SELECT RPAD (INITCAP(LOWER(Name)),70,.),
• SUBSTR (SSN,5)
• FROM Student
• SELECT Name
• FROM Student
• WHERE SUBSTR (SSN,5,2)80
• SELECT Name, SUBSTR (SSN,-4)
• FROM Student

13
List(Characters)

• SELECT Name, INSTR (Name,R)
• FROM Student
•   ----------------------------------------
  --------
• MARY 3
• JOHN 0
• ROBIN 1
• SELECT Name, INSTR (Name,R,1,2)
• FROM Student
• SELECT Name, INSTR(Address,FROSTBURG)
• FROM Student

14
List(Characters)

• Character Manipulations
• REPLACE(string, searchSt ,replace)
• REPLACE(address,21532, 21211)
• TRANSLATE (string, fromSt, toSt)
• TRANSLATE(12345678, 123, 999)
• ASCII(string)
• ASCII(A)

15
List(Characters)

• Character Manipulations
• SOUNDEX
• SELECT name, GPA
• FROM student
• WHERE
• SOUNDEX (name)SOUNDEX(LAVALE)

16
Character Manipulation

• New Functions
• REGEXP_SUBSTR
• REGEXP_INSTR
• REGEXP_LIKE
• REGEXP_REPLACE

17
LIST(Numbers)

• ROUND (value, precision)
• ROUND(234.1161,2)
• TRUNC(value, precision)
• TRUNC(234.1161,2)
• POWER(value,exponent)
• POWER(3,2) 
• MOD(value1, value2)
• MOD(900,400)

18
LIST(Numbers)

• SELECT ROUND(Salary,1)
• FROM Faculty
• SELECT TRUNC(234.111,2),
• FROM DUAL
• TRUNC(234.567)
• TRUNC(234.5678,-2)

19
DATE

• Date is stored in an internal numeric format
  century, year, month, day, hours, minutes, second
• Default date is DD-MON-YY
• SYSDATE
• CURRENT_DATE
• STSTIMESTAMP
• 25-OCT-04 04.15.31.652000 PM

20
DATE

• Example
• List the ages of students
•  
• SELECT name, SYSDATE - B_Date
• FROM student

21
Date

• Date number
• Date number
• Date date
• Date number/24

22
DATE

• MONTHS_BETWEEN(day1,day2)
• SELECT name, MONTHS_BETWEEN(SYSDATE , B_Date)
  age_in_month
• FROM Student

23
DATE

• ADD_MONTHS (date,n)
• SELECT name, ADD_MONTHS(B_Date,5) age
• FROM Student
• SELECT name, ADD_MONTHS(B_Date,-15) age
• FROM Student

24
DATE

• ROUND(date ,fmt)
•  
• SELECT name,
• ROUND (B_Date,MONTH)
• FROM Student
• SELECT name, ROUND(B_Date,YEAR)
• FROM Student

25
DATE

• ROUND(date ,fmt)
•  
• SELECT 25-OCT-04 - ROUND (SYSDATE)
• FROM DOAL
• SELECT name, ROUND(B_Date,YEAR)
• FROM Student

26
DATE

• NEXT_DAY
• SELECT cycledate
• FROM paydate
• SELECT NEXT_DAY(cycledate, FRIDAY)
• FROM paydate

27
DATE

• LAST_DAY (date)
• SELECT LAST_DAY (SYSDATE) Last,
• SYSDATE, LAST_DAY (SYSDATE) SYSDATE Left
• FROM DUAL
• Last SYSDATE Left
• -------------------------------------------------
  -------
• 31-MAY-05 28-MAY-05 4

28
Conversion Function

• Implicit conversion (Automatic)
• CHAR or VARCHAR2 to NUMBER
• CHAR or VARCHAR2 to DATE
• NUMBER to VARCHAR2
• DATE to VARCHAR2

29
Conversion Function

• Explicit datatype conversion
• TO_CHAR (NUMBER ,fmt )
• TO_CHAR (DATE ,fmt )
• TO_DATE (CHAR ,fmt )
• TO_NUMBER (CHAR ,fmt )

30
Conversion Function

• SELECT TO_CHAR(b_date,MM/YY)
• FROM student
• Format
• YYYY
• YYY
• YY
• RR
• YEAR
• MM
• MONTH
• DD
• DY
• DAY
• HH HH12 HH24
• MI
• SS

31
Conversion Function

• EXTRACT (YEAR FROM B_Date)
• YEAR
• MONTH
• DAY
• HOUR
• MINUTE
• SECOND
• SELECT EXTRACT (YEAR FROM DATE 1999-01-08)
• FROM DUAL --1999
• SELECT EXTRACT (YEAR FROM TO_DATE
• (B_Date, DD-MON-YY)
• FROM Student

32
Conversion Function

• SELECT SUBSTR(TO_CHAR(111223333),1,3) -
  
• SUBSTR (TO_CHAR(111223333),4,2) -
• SUBSTR(TO_CHAR(111223333),6)
• FROM Student

33
Conversion Function

• SELECT SUBSTR(ssn,1,3)
• -
• SUBSTR(ssn,4,2)
• -
• SUBSTR(ssn,6)
• FROM Student

34
CAST

• CAST (expression) AS (Type)
• CHAR NUMBER DATE
  ROW ROWID
• VARCHAR2 TIMESTAMP
• 1 X X X
  X X
• 2 X X
  
• 3 X X
  
• 4 X
  X
• 5 X
  X
• CAST (B_Date AS CHAR)

35
NULLIF

• NULLIF (Exp1, Exp2)
• IF Exp1 Exp2 THEN RETURN
• NULL
• ELSE RETURN
• Exp1
• SELECT Name, NULLIF (a.major, b.major)
• oldMajor
• FROM Student a, StudentHistory b
• WHERE a.ID b.ID

36
NVL

• NVL (Exp1,Exp2)
• SELECT Name, NVL
• (TO_CHAR (Commission_pct, Not Applicable))
  commission
• FROM Sales

37
NVL2

• NVL2 (Exp1, Exp2, Exp3)
• SELECT Name,
• NVL2 (Commission_pct, Salary Salary
  Commission_pct, Salary)
• FROM Employee

38
COALESCE

• COALESCE (exp_List)
• Return the first non_null expression in the
  expression list
• COALESCE (Email, Address, Phone_, Phone_W)
• SELECT Product_ID, Product_Name, List_Price,
  Min_Price, COALESCE (List_Price, Min_Price, 10)
  Sale Price
• FROM Product

39
Use of DECODE

• DECODE
• DECODE (col/exp, compare1, result1
• ,compare2, result2,
• ,default )
• SELECT name, salary,
• DECODE (Dept, COSC, salary2.2,
• MATH, salary1.2,
• ART, salary0.2,
• salary)
• FROM Faculty