SingleRow Functions SQL Functions are a very powerful

Single-Row Functions

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

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

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

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, [arg 1, arg 2, . . . ])

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

• 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 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

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

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 SQL> SELECT 2 FROM 3 WHERE empno, ename, deptno emp LOWER(ename) = 'blake'; EMPNO ENAME DEPTNO ----------7698 BLAKE 30

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.

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

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.

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 -----MARTIN ALLEN TURNER WARD CONCAT(ENAME, JOB) LENGTH(ENAME) INSTR(ENAME, 'A') ---------------MARTINSALESMAN 6 2 ALLENSALESMAN 5 1 TURNERSALESMAN 6 0 WARDSALESMAN 4 2

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

Number Functions l ROUND: Rounds value to specified decimal ROUND(45. 926, 2) l TRUNC: Truncates value to specified decimal TRUNC(45. 926, 2) l 45. 93 MOD: MOD(1600, 300) 45. 92 Returns remainder of division 100

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

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, -1) --------------45. 92 45 40

Using the MOD Function Calculate the remainder of the ratio of salary to commission for all employees whose job title is SQL> SELECT ename, sal, comm, MOD(sal, comm) salesman. 2 3 FROM WHERE emp job = 'SALESMAN'; ENAME SAL COMM MOD(SAL, COMM) -----------MARTIN 1250 1400 1250 ALLEN 1600 300 100 TURNER 1500 0 1500 WARD 1250 500 250

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

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

You can perform the following operations:

Using Arithmetic Operators with Dates SQL> SELECT ename, (SYSDATE-hiredate)/7 WEEKS 2 FROM emp 3 WHERE deptno = 10; ENAME -----KING CLARK MILLER WEEKS ----830. 93709 853. 93709 821. 36566

Date Functions Function Description MONTHS_BETWEEN Number of months between 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

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'

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, 2 MONTHS_BETWEEN(SYSDATE, hiredate) TENURE, 3 ADD_MONTHS(hiredate, 6) REVIEW, 4 LAST_DAY(hiredate) 5 FROM emp 6 WHERE MONTHS_BETWEEN (SYSDATE, hiredate)<200;

Using Date Functions • 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

Example Compare 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> SELECT 2 empno, hiredate, ROUND(hiredate, 'MONTH'), TRUNC(hiredate, 'MONTH') 3 FROM emp 4 WHERE hiredate like '%82';