SINGLE-ROW FUNCTIONS Lecture 9 SQL Functions Functions are very powerful feature of SQL and can be used to do the following: Perform a calculation on data Modify individual data items
Manipulate output of groups of rows Format dates and numbers for display Convert column data types Two Types of SQL Functions There are two distinct types of 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 Multiple-Row Functions Functions can manipulate groups of rows to give one result per group of rows. These functions are known as group functions Single-Row Functions Single row functions:
Manipulate data items Accept arguments and return one value Act on each row returned Return one result per row May modify the data type Can be nested Can be used in SELECT, WHERE, and ORDER BY clauses
Accept arguments which can be a column or an expression Syntax: function_name [(arg1, arg2,...)] Single-Row Functions This lesson covers the following single -row functions: Character functions: accept character input and can return both character and number values
Number functions: Accept numeric input and return numeric values Character Functions Character Functions Casemanipulation 1. LOWER 2. UPPER
3. INITCAP Charactermanipulation 1. SUBSTR 2. LENGTH Character Functions: Case Manipulation Functions
These functions convert case for character strings. See (Example 1, Example 2) Function result LOWER(SQL Course)
sql course UPPER(SQL Course) SQL COURSE INITCAP(SQL Course) Sql Course
Character Functions: Case Manipulation Functions Example1: SELECT 'The job id for '||UPPER(last_name)||' is ' ||LOWER(job_id) AS "EMPLOYEE DETAILS" FROM employees;
Example2: Display the employee number, name, and department number for employee Higgins: Character Functions: Character Manipulation Functions Function
Purpose LENGTH(Column|expression) Returns the number of characters in the expression SUBSTR(column|expression,m [,n])
Returns specified characters from character value starting at character position m,n character long (if m is negative the count starts and the end of the character value . If n is omitted all characters to the end of the string are returned Character Functions:
Character Manipulation Functions These functions manipulate character strings. For example: Function Result LENGTH('HelloWorld')
10 SUBSTR('HelloWorld',1,5) Hello Character Functions: Character Manipulation Functions (Cont.)
Example: SELECT employee_id, job_id,LENGTH (last_name) FROM employees WHERE SUBSTR(job_id, 4) = 'REP'; Number Functions ROUND: Rounds value to specified decimal (Example 3) Syntax: ROUND(column|expression, n) :Rounds the column, expression, or
value to n decimal places, or, if n is omitted, no decimal places. Example: ROUND(45.926, 2) 45.93 Number Functions TRUNC: Truncates value to specified decimal (Example 4)
Syntax: TRUNC(column|expression,n) Truncates the column, expression, or value to n decimal places, or, if n is omitted, then n defaults to zero Example: TRUNC(45.926, 2) 45.92
Number Functions MOD: Returns remainder of division (Example 5) Syntax: MOD(m,n) Returns the remainder of m divided by n Example: MOD(1600, 300) 100
Example 3 Example 4 Example 5