Single Row Functions Part I

Single Row Functions Part I Week 2

Objectives • Describe types of single row functions in SQL • Describe and use character, number and date SQL functions

arg 1 arg 2 Result arg n Functions Function • All functions input 0, 1 or more arguments and output a single result. • 2 basic types in SQL: Single row and Group

Single Row Functions • Act on each individual row selected • Return one result per row retrieved from table • 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, …

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('Intro to SQL') UPPER('Intro to SQL') INITCAP('Intro to SQL') intro to sql INTRO TO SQL Intro to Sql

Case Conversion Examples SQL> SELECT employee_id, last_name, department_id FROM employees WHERE LOWER(last_name) = ‘king'; EMPLOYEE_ID LAST_NAME DEPARTMENT_ID 100 King 90 Display employee number, name and department number for employees named King SQL> SELECT employee_id, last_name, department_id FROM employees WHERE last_name = ‘king'; no rows selected SQL> SELECT employee_id, last_name, department_id FROM employees WHERE last_name = UPPER(‘King‘); no rows selected

Case Conversion Examples (ctd) SQL> SELECT employee_id, last_name, department_id FROM employees WHERE INITCAP(last_name) = ‘King'; SQL> SELECT empno, last_name, department_id FROM employees WHERE UPPER(last_name) = ‘KING'; EMPLOYEE_ID LAST_NAME DEPARTMENT_ID 100 King 90 EMPLOYEE_ID LAST_NAME DEPARTMENT_ID 100 King 90 Display employee number, name and department number for employees named King

Case Conversion (ctd) SQL> SELECT employee_id, last_name, department_id FROM employees WHERE UPPER(last_name) = UPPER(‘&last_name’); EMPLOYEE_ID LAST_NAME DEPARTMENT_ID 100 King 90 Recommended: always use a case conversion function with column character data (except for unusual circumstances where case is significant in the data) and also always use a case conversion function with character substitution variables (except for unusual circumstances where case is significant in the data)

Character Manipulation Functions Character Manipulation Functions Character Manipulation Functions Character Manipulation Functions Character Manipulation Functions Character Manipulation Functions Character Manipulation Functions • SUBSTR(string, start_position, length):Extracts a string of determined length from a specified starting position • CONCAT(string1, string2):Joins 2 character strings together (You are limited to using only two parameters with CONCAT) • 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 occurrences of a leading and/or trailing (default is both leading and trailing) character (a blank is the default character) from a string

Examples using Character Manipulation Functions Function Result CONCAT('Good', 'Day') SUBSTR('Good',3,2) LENGTH('Good') INSTR('Good', 'o') LPAD(last_name,20,'*') TRIM(‘$‘ FROM ‘$2,345‘) GoodDay od 4 2 ****************King 2,345

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

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

Single Row Functions Part I