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 || ‘--->‘ || Address , GPA FROM Student;

9. List(Characters): • SELECTRPAD (Name, 40, ‘.’), GPA FROM Student; JOHN............... 3.1 MARY ............. 3.2 • SELECTRPAD (Name, 20), GPA FROM Student; MARY 3.2 MOHAMMAD 3.3 • SELECTLPAD (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)<20; • 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’, salary*2.2, ‘MATH’, salary*1.2, ‘ART’, salary*0.2, salary) FROM Faculty;