SQL FUNCTIONS

1. SQL FUNCTIONS NUMERIC, CHARACTER AND DATEFUNCTIONS

2. SQL functions are built into Oracle Database and are available for use in various appropriate SQL statements. Do not confuse SQL functions with user-defined functions written in PL/SQL. If you call a SQL function with an argument of a data type other than the data type expected by the SQL function, then Oracle attempts to convert the argument to the expected data type before performing the SQL function. About Function

3. Input Output arg 1 arg 2 Result value arg n SQL Functions Function Function performs action

4. Two Types of SQL Functions Functions Multiple-row functions Single-row functions

5. Single-row functions return a single result row for every row of a queried table or view. These functions can appear in select lists, WHERE clauses, START WITH and CONNECT BY clauses, and HAVING clauses. Single Row Function

6. Numeric functions accept numeric input and return numeric values. Most numeric functions return NUMBER values that are accurate to 38 decimal digits. The transcendental functions COS, COSH, EXP, LN, LOG, SIN, SINH, SQRT, TAN, and TANH are accurate to 36 decimal digits. The transcendental functions ACOS, ASIN, ATAN, and ATAN2 are accurate to 30 decimal digits. Numeric Functions

7. Single-Row Functions Character General Number Single-row functions Conversion Date

8. FLOOR • POWER • MOD • ROUND • TRUNC • …… Number Functions

9. Syntax: • Purpose: • FLOOR returns the largest integer equal to or less than n. • The number n can always be written as the sum of an integer k and a positive fraction f such that 0 <= f < 1 and n = k + f. • The value of FLOOR is the integer k. Thus, the value of FLOOR is n itself if and only if n is precisely an integer. FLOOR Function FLOOR(n)

10. The following example returns the largest integer equal to or less than 15.7: Example of Using FLOOR Function SELECT FLOOR(15.7) "Floor" FROM DUAL; Floor …………….. 15

11. Syntax: • Purpose: • POWER returns n2 raised to the n1 power. The base n2 and the exponent n1 can be any numbers, but if n2 is negative, then n1 must be an integer. POWER Function POWER(n2, n1) SELECT POWER(3, 2) “POWER" FROM DUAL; POWER …………….. 9

12. Syntax: • Purpose: • MOD returns the remainder of n2 divided by n1. Returns n2 if n1 is 0. • This function takes as arguments any numeric data type or any nonnumeric data type that can be implicitly converted to a numeric data type. Mod Function MOD(n2, n1)

13. Example of Using MOD Function SQL> SELECT ename, sal, comm, MOD(sal, comm) 2 FROM emp 3 WHERE job = 'SALESMAN'; ENAME SAL COMM MOD(SAL,COMM) ---------- --------- --------- ------------- MARTIN 1250 1400 1250 ALLEN 1600 300 100 TURNER 1500 0 1500 WARD 1250 500 250

14. Syntax: • Purpose: • ROUND returns n rounded to integer places to the right of the decimal point. • If you omit integer, then n is rounded to zero places. • If integer is negative, then n is rounded off to the left of the decimal point. • n can be any numeric data type or any nonnumeric data type that can be implicitly converted to a numeric data type. ROUND Function ROUND(n, integer)

15. ROUND is implemented using the following rules: 1. If n is 0, then ROUND always returns 0 regardless of integer. 2. If n is negative, then ROUND(n, integer) returns -ROUND(-n, integer). 3. If n is positive, then ROUND(n, integer) = FLOOR(n * POWER(10, integer) + 0.5) * POWER(10, -integer ROUND Function(Cont.)

16. The following example rounds a number to one decimal point: Example of Using ROUND Function SELECT ROUND(15.193,1) "Round" FROM DUAL; Round ……….. 15.2 SELECT ROUND(15.193,-1) "Round" FROM DUAL; Round ……….. 20

17. Using the ROUND Function(2) SQL> SELECT ROUND(45.923,2), ROUND(45.923,0), 2 ROUND(45.923,-1) 3 FROM DUAL; ROUND(45.923,2) ROUND(45.923,0) ROUND(45.923,-1) --------------- -------------- ----------------- 45.92 46 50

18. Syntax: • Purpose: • The TRUNC (number) function returns n1 truncated to n2 decimal places. • If n2 is omitted, then n1 is truncated to 0 places. n2 can be negative to truncate (make zero) n2 digits left of the decimal point. TRUNC Function TRUNC(n1, n2)

19. Example of Using the TRUNC Function SQL> SELECT TRUNC(45.923,2), TRUNC(45.923), 2 TRUNC(45.923,-1) 3 FROM DUAL; TRUNC(45.923,2) TRUNC(45.923) TRUNC(45.923,-1) --------------- ------------- --------------- 45.92 45 40

20. Character Functions Characterfunctions Case conversion functions Character manipulation functions LOWER UPPER INITCAP Return Character value Return Number Value CONCAT SUBSTR LPAD TRIM ASCII LENGTH INSTR

21. Character functions that return character values return values of the following data types unless otherwise documented: • If the input argument is CHAR or VARCHAR2, then the value returned is VARCHAR2. • If the input argument is NCHAR or NVARCHAR2, then the value returned is NVARCHAR2. The length of the value returned by the function is limited by the maximum length of the data type returned. • For functions that return CHAR or VARCHAR2, if the length of the return value exceeds the limit, then Oracle Database truncates it and returns the result without an error message. • For functions that return CLOB values, if the length of the return values exceeds the limit, then Oracle raises an error and returns no data. Character Functions Returning Character Values

22. Case Conversion Functions • Convert case for character strings Function Result LOWER('SQL Course') UPPER('SQL Course') INITCAP('SQL Course') sql course SQL COURSE Sql Course

23. Display the employee number, name, and department number for employee Blake. Using Case Conversion Functions SQL> SELECT empno, ename, deptno 2 FROM emp 3 WHERE ename = 'blake'; no rows selected SQL> SELECT empno, ename, deptno 2 FROM emp 3 WHERE ename = UPPER('blake'); EMPNO ENAME DEPTNO --------- ---------- --------- 7698 BLAKE 30

24. Character Manipulation Functions • Manipulate character strings Function Result GoodString Str ******5000 MITH CONCAT('Good', 'String') SUBSTR('String',1,3) LPAD(sal,10,'*') TRIM('S' FROM 'SSMITH')

25. Using the Character Manipulation Functions SELECT ename, CONCAT (ename, job), LENGTH(ename), INSTR(ename, 'A') From EMP WHERE SUBSTR(job,1,5) = 'SALES'; ENAME CONCAT(ENAME,JOB) LENGTH(ENAME) INSTR(ENAME,'A') ---------- ------------------- ------------- ---------------- MARTIN MARTINSALESMAN 6 2 ALLEN ALLENSALESMAN 5 1 TURNER TURNERSALESMAN 6 0 WARD WARDSALESMAN 4 2

26. Character functions that return number values can take as their argument any character data type. The character functions that return number values are: • ASCII • INSTR • LENGTH • REGEXP_COUNT • REGEXP_INSTR Character Functions Returning Number Values

27. Syntax: • Purpose • ASCII returns the decimal representation in the database character set of the first character of char. • char can be of data type CHAR, VARCHAR2, NCHAR, or NVARCHAR2. The value returned is of data type NUMBER ASCII ASCII(char)

29. The following example returns employees whose last names begin with the letter L, whose ASCII equivalent is 76: Example of Using ASCII Function SQL> SELECT last_name FROM employees WHERE ASCII(SUBSTR(last_name, 1, 1)) = 76 ORDER BY last_name; LAST_NAME --------- Ladwig Landry Lee

30. Manipulate character strings Character Manipulation Functions Function Result 6 3 LENGTH('String') INSTR('String', 'r')

31. Oracle stores dates in an internal numeric format: century, year, month, day, hours, minutes, seconds. • The default date format is DD-MON-YY. • SYSDATE is a function returning date and time. • DUAL is a dummy table used to view SYSDATE. Working with Dates

32. Add or subtract a number to or from a date for a resultant date value. • Subtract two dates to find the numberof days between those dates. • Add hours to a date by dividing the number of hours by 24. Arithmetic with Dates

33. Using Arithmetic Operatorswith Dates SQL> SELECT ename, (SYSDATE-hiredate)/7 WEEKS 2 FROM emp 3 WHERE deptno = 10; ENAME WEEKS ---------- --------- KING 830.93709 CLARK 853.93709 MILLER 821.36566

34. Date Functions Function Description MONTHS_BETWEEN Number of monthsbetween two dates ADD_MONTHS Add calendar months to date NEXT_DAY Next day of the date specified LAST_DAY Last day of the month ROUND Round date TRUNC Truncate date

35. Using Date Functions • MONTHS_BETWEEN ('01-DEC-09',‘01-NOV-09) 1 • ADD_MONTHS ('11-JAN-09',6) '11-JUL-09' • NEXT_DAY (‘20-NOV-09','FRIDAY') ‘27-NOV-09' • LAST_DAY('01-SEP-95') '30-SEP-95'

36. Using Date Functions • SELECT empno, hiredate, • MONTHS_BETWEEN(SYSDATE, hiredate) TENURE,ADD_MONTHS(hiredate, 6) REVIEW,NEXT_DAY(hiredate, 'FRIDAY'), LAST_DAY(hiredate) • FROM emp • WHERE MONTHS_BETWEEN (SYSDATE, hiredate)<300;

37. ROUND('25-JUL-95','MONTH') 01-AUG-95 • ROUND('25-JUL-95','YEAR') 01-JAN-96 • TRUNC('25-JUL-95','MONTH') 01-JUL-95 • TRUNC('25-JUL-95','YEAR') 01-JAN-95 Using Date Functions

38. Example • Compare the hire dates for all employees who started in 1982. Display the employee number, hiredate, and month started using the ROUND and TRUNC functions. Using Date Functions • SELECT empno, hiredate, • ROUND(hiredate, 'MONTH'), TRUNC(hiredate, 'MONTH') • FROM emp • WHERE hiredate like '%82';

39. Conversion Functions Datatype conversion Implicit datatype conversion Explicit datatype conversion

40. Conversion functions convert a value from one data type to another. Generally, the form of the function names follows the convention datatype TO datatype. The first data type is the input data type. The second data type is the output data type. Conversion Functions