Single-Row Functions

1. Single-Row Functions

3. SQL Functions • Functions are a very powerful feature of SQL and can be used to do the following: • Perform calculations on data • Modify individual data items • Manipulate output for groups of rows • Format dates and numbers for display • Convert column datatypes • SQL functions may accept arguments and always return a value. • Note: Most of the functions described in this lesson are specific to Oracle’s version of SQL.

4. Two Types of SQL Functions Functions Multiple-row functions Single-row functions • There are two distinct types of functions: • Single-row functions • Multiple-row functions

5. Single-Row Functions • These functions operate on single rows only and return one result per row. There are different types of single-row functions. This lesson covers the following ones: • Character - Number - Date - Conversion • Single-row functions are used to manipulate data items. They accept one or more arguments and return one value for each row returned by the query. An argument can be one of the following: • User-supplied constant • Variable value • Column name • Expression

6. Features of single-row functions: • Act on each row returned in the query • Return one result per row • May return a data value of a different type than that referenced • May expect one or more arguments • Can be used in SELECT, WHERE, and ORDER BY clauses; can be nested function_name (column|expression, [arg1, arg2,...])

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

8. Character functions: Accept character input and can return both character and number values • Number functions: Accept numeric input and return numeric values • Date functions: Operate on values of the date datatype (All date functions return a value of date datatype except the MONTHS_BETWEEN function, which returns a number.) • Conversion functions: Convert a value from one datatype to another • General functions: • NVL function • DECODE function

9. Character Functions Character functions Character manipulation functions Case conversion functions LOWER UPPER INITCAP CONCAT SUBSTR LENGTH INSTR LPAD

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

12. Example: Display the values in the columns; ename, job, at the same field with a space btween them displying them in lowercase, under a new column name as Employee Details. More over the values in the column job should be initial capitalized and displayed under the column name Employee Job the values in the column ename should be in uppercase and displayed under the column name as Employee name for all the employees whose name starts with letter A.

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

15. Character Manipulation Functions • CONCAT:Joins values together (You are limited to using two parameters with CONCAT.) • SUBSTR:Extracts a string of determined length • LENGTH:Shows the length of a string as a numeric value • INSTR:Finds numeric position of a named character • LPAD:Pads the character value right-justified • Trim: Trims heading or trailing characters (or both) from a character string if trim_character or trim_source is a character literal. You must enclose it in single quotes.

16. Using the Character Manipulation Functions SQL> SELECT ename, CONCAT (ename, job), LENGTH(ename), 2 INSTR(ename, 'A') 3 FROM emp 4 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

17. Example Modify the SQL statement on the slide to display the data for those employees whose names end with an N.

20. Number Functions • ROUND: Rounds value to specified decimal ROUND(45.926, 2) 45.93 • TRUNC: Truncates value to specified decimal TRUNC(45.926, 2) 45.92 • MOD: Returns remainder of division MOD(1600, 300) 100

22. Using the ROUND Function 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

24. 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

27. Using the MOD Function • Calculate the remainder of the ratio of salary to commission for all employees whose job title is salesman. 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

28. Working with Dates • 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.

30. Arithmetic with Dates • 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.

31. You can perform the following operations:

32. 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

33. 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

34. Using Date Functions • MONTHS_BETWEEN ('01-SEP-95','11-JAN-94') 19.6774194 • ADD_MONTHS ('11-JAN-94',6) '11-JUL-94' • NEXT_DAY ('01-SEP-95','FRIDAY') '08-SEP-95' • LAST_DAY('01-SEP-95') '30-SEP-95'

35. e.g. (using date functions ) • Date Functions (continued) • For all employees employed for fewer than 200 months, display the employee number, hire date, number of months employed, six-month review date, and last day of the month when hired. SQL>SELECT empno, hiredate, 2MONTHS_BETWEEN(SYSDATE, hiredate) TENURE, 3ADD_MONTHS(hiredate, 6) REVIEW, 4LAST_DAY(hiredate) 5 FROMemp 6 WHEREMONTHS_BETWEEN (SYSDATE, hiredate)<200;

36. 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

41. ExampleCompare the hire dates for all employees who started in 1982. Display the employee number, hire date, and month started using the ROUND and TRUNC functions. SQL> SELECTempno, hiredate, 2ROUND(hiredate, 'MONTH'), TRUNC(hiredate, 'MONTH') 3 FROMemp 4 WHEREhiredate like '%82';