chapter 5 selected single-row functions. chapter objectives use the upper, lower, and initcap...
Post on 30-Dec-2015
225 Views
Preview:
TRANSCRIPT
Chapter 5Selected Single-Row
Functions
Chapter 5Selected Single-Row
Functions
TerminologyTerminology
Function – predefined block of code that accepts arguments– Single-row Function – returns one row of
results for each record processed– Multiple-row Function – returns one result
per group of data processed
Function – predefined block of code that accepts arguments– Single-row Function – returns one row of
results for each record processed– Multiple-row Function – returns one result
per group of data processed
Types of FunctionsTypes of Functions
Case Conversion FunctionsCase Conversion Functions
Alter the case of data stored in a column or character stringAlter the case of data stored in a column or character string
LOWER FunctionLOWER Function
Used to convert characters to lower-case lettersUsed to convert characters to lower-case letters
UPPER FunctionUPPER Function
Used to convert characters to upper-case lettersUsed to convert characters to upper-case letters
INITCAP FunctionINITCAP Function
Used to convert characters to mixed-caseUsed to convert characters to mixed-case
Character Manipulation FunctionsCharacter Manipulation Functions
Manipulates data by extracting substrings, counting number of characters, replacing strings, etc.
Manipulates data by extracting substrings, counting number of characters, replacing strings, etc.
SUBSTR FunctionSUBSTR Function
Used to return a substring, or portion of a stringUsed to return a substring, or portion of a string
LENGTH FunctionLENGTH Function
Used to determine the number of characters in a stringUsed to determine the number of characters in a string
LTRIM and RTRIM FunctionsLTRIM and RTRIM Functions
Used to remove a specific string of charactersUsed to remove a specific string of characters
REPLACE FunctionREPLACE Function
Substitutes a string with another specified stringSubstitutes a string with another specified string
CONCAT FunctionCONCAT Function
Used to concatenate two character stringsUsed to concatenate two character strings
ROUND FunctionROUND Function
Used to round numeric columns to a stated precision
Used to round numeric columns to a stated precision
MONTHS_BETWEEN FunctionMONTHS_BETWEEN Function
Determines the number of months between two datesDetermines the number of months between two dates
ADD_MONTHS FunctionADD_MONTHS Function
Adds a specified number of months to a dateAdds a specified number of months to a date
NEXT_DAY FunctionNEXT_DAY Function
Determines the next occurrence of a specified day of the week after a given date
Determines the next occurrence of a specified day of the week after a given date
DECODE FunctionDECODE Function
Determines action based upon values in a listDetermines action based upon values in a list
Nesting FunctionsNesting Functions
One function is used as an argument inside another function
Must include all arguments for each function Inner function is resolved first, then outer
function
One function is used as an argument inside another function
Must include all arguments for each function Inner function is resolved first, then outer
function
top related