Download
1 / 21
220 likes | 505 Views
Single Row Functions. Objectives. Use character, number, and date functions Use conversion functions Describe types of single row functions in SQL. arg 1. arg 2. Result. arg n. Functions. 2 basic types: Single row and Group
E N D
Objectives • Use character, number, and date functions • Use conversion functions • Describe types of single row functions in SQL
arg 1 arg 2 Result arg n Functions • 2 basic types: Single row and Group • All functions input 1 or more arguments and output a single result: Function
Single Row Functions • Act on each individual row selected • Return one result per row • Can be nested • Function can be used as part of expression in: SELECT clause, WHERE clause, ORDER BY clause, … • Argument for function may be any expression (literals, columns arithmetic operators, … • Format: function_name (column|expression, [arg1, arg2,...])
Types of Single Row Functions Character General Number Single-row functions Conversion Date
Single Row Character Functions Character functions Case conversion functions Character manipulation functions CONCAT SUBSTR LENGTH INSTR LPAD TRIM LOWER UPPER INITCAP
Case Conversion Functions • Convert case of character string data • Useful for matching when unsure of case used for column’s data or when case varies by row by row • UPPER: converts all characters to upper case • LOWER: converts all characters to lower case • INITCAP: converts first character of each word to upper case and remaining to lower case Function Result LOWER('SQL course') UPPER('SQL course') INITCAP('SQL course') sql course SQL COURSE Sql Course
Case Conversion Examples SQL> SELECT emp_no, ename, dept_no FROM employees WHERE LOWER(ename) = ‘king'; EMP_NO ENAME DEPT_NO --------- ---------- --------- 109 King 10 Display employee number, name and department number for employees named King SQL> SELECT emp_no, ename, dept_no FROM employees WHERE ename = ‘king'; no rows selected SQL> SELECT emp_no, ename, dept_no FROM employees WHERE ename = UPPER(‘King‘); no rows selected
Case Conversion Examples (ctd) SQL> SELECT emp_no, ename, dept_no FROM employees WHERE INITCAP(ename) = ‘King'; SQL> SELECT empno, ename, dept_no FROM employees WHERE UPPER(ename) = ‘KING'; EMP_NO ENAME DEPT_NO --------- ---------- --------- 109 King 10 EMP_NO ENAME DEPT_NO --------- ---------- --------- 109 King 10 Display employee number, name and department number for employees named King
Character Manipulation Functions Character Manipulation Functions Character Manipulation Functions Character Manipulation Functions Character Manipulation Functions Character Manipulation Functions Character Manipulation Functions • CONCAT(string1, string2):Joins 2 character strings together (You are limited to using only two parameters with CONCAT) • SUBSTR(string, start_position, length):Extracts a string of determined length from a specified starting position • LENGTH(string):Shows the length of a string as a numeric value • INSTR(string, character):Finds numeric position of first occurrence of a specified character • LPAD(string, length, character):Places occurrences of a character (a blank is the default character) to the left of a string to end up with a specified length of character string • RPAD: pads a character value right-justified on a string • TRIM([{LEADING | TRAILING | BOTH}] character FROM string): removes leading and/or trailing character (a blank is the default character) from a string
Examples using Character Manipulation Functions Function Result CONCAT('Good', 'Day') SUBSTR('Good',2,3) LENGTH('Good') INSTR('Good', 'o') LPAD(ename,20,'*') TRIM(‘ 2,345‘, ’,’) GoodDay ood 4 2 ****************King 2345
Number Functions • Manipulate numeric values; frequently used functions include: ROUND, TRUNC, MOD • ROUND(number, n): rounds number to n decimal places • ROUND(43.826, 2) 43.83 • ROUND(43.826, 0) 44 • ROUND(43.826, -1) 40 • TRUNC(number, n): truncates value to n decimal places • TRUNC(43.826, 2) 43.82 • TRUNC(43.826, 0) 43 • TRUNC(43.826, -1) 40 • MOD(number1, number2): returns remainder of number1 divided by number2 • MOD(17, 3) 2
Date Functions • manipulate date data: most perform calculations on dates • ADD_MONTHS(date, number): add or subtract a number of months from a date • ADD_MONTHS(’22-JAN-01’,6) 22-JUL-01 • MONTHS_BETWEEN(date1, date2) : number of months between dates • MONTHS_BETWEEN(’22-JAN-01’, ’22-JUL-01) -6 • NEXT_DAY(date, day) returns the date for the next ‘day of the week’ from the date specified • NEXT_DAY(’22-JAN-01’,’FRIDAY’) 26-JAN-01 • LAST_DAY(date) returns the last day of the month for the date given • NEXT_DAY(’22-JAN-01’) 31-JAN-01
Date Functions (ctd) • ROUND function can also be used on dates, rounding a date to the nearest month or year • ROUND(’22-JAN-01’, ‘MONTH’) 01-FEB-01 • ROUND(’22-JAN-01’,’YEAR’) 01-JAN-01 • TRUNC function can also be used on dates, truncating a date to the nearest month or year • TRUNC(’22-JAN-01’, ‘MONTH’) 01-JAN-01 • TRUNC(’22-JAN-01’,’YEAR’) 01-JAN-01
General Functions • NVL(column, value): used to provide an actual value in place of a NULL value for a column • SELECT emp_no, (NVL(sal,1000) + NVL(comm,0)) AS COMPENSATION • FROM employees • DECODE(expression, condition1, result1 [, condition2, result2], … [, default]): operates similarly to an IF- THEN-ELSE statement in evaluating values in a column • SELECT emp_no, DECODE(Job,’President’,1, ‘Manager’, 2, ‘Developer’, 3, Analyst’, 3, ‘Clerk’, 4) AS “JOB RANKING” • FROM employees • SYSDATE: returns current system date • SELECT emp_no, hiredate • FROM employees • WHERE hiredate < ADD_MONTHS(SYSDATE – 6)
Conversion Functions • many conversions in SQL occur implicitly, for example string || numeric string • however in some cases a conversion function must be used • TO_NUMBER(string, [format]): converts strings into numeric data - often used if numeric values have been stored using formatting of decimal points and numeric separators • TO_NUMBER(‘1,234.56’, ‘9,999.99’) • TO_CHAR(numeric data, [‘format’]): converts numeric data to character data – normally used to format numeric data (if format is not large enough to represent data then # symbols will be shown) • TO_CHAR(sal, ‘$99,999.99’)
Conversion Functions(ctd) • TO_CHAR(date data, [‘format’]): converts date data to character data – normally used to format date data – if format is not large enough to represent data then # symbols will be shown • TO_CHAR(’22-JAN-01’, ‘DAY MONTH DD, YYYY’) THURSDAY JANUARY 22, 2001 Date Format Symbols Day: Day of the week DY: Abbreviated day of the week DD: 2 digit day value MM: 2 digit month value Mon: Abbreviated month name Month: Month name YYYY: 4 digit year value Year: Year value in words Q: Quarter of the year (1st – 4th)
Conversion Functions (ctd) • TO_DATE(string, ‘format’): converts strings into data in Oracle’s internal date format • SELECT emp_no, hiredate • FROM employees • WHERE hiredate = TO_DATE(‘September 28, 1981’, ‘Month DD, YYYY’)
Nesting of Functions • Single row function calls can be nested within each other • Innermost function is evaluated first • Example – show the review dates for all employees where review date is first Monday occurring 3 months after starting work: • SELECT emp_no, • NEXT_DAY(ADD_MONTHS(hiredate, 3) , ‘MONDAY’) • AS “FIRST REVIEW DATE”, • (NVL(sal,0) + NVL(comm,0)) AS COMPENSATION • FROM employees;
Querying Data Not Found in a Table • Sometimes you may want to display data not stored in a table • However format of SELECT statement requires a FROM clause • In these situations refer to a small public table called DUAL which consists of one row of data for a single column and which therefore results in a single row of output • Example: display today’s date: • SELECT SYSDATE • FROM DUAL;
