Chapter 5 Selected Single-Row Functions

1. Chapter 5Selected Single-Row Functions Introduction to Oracle9i: SQL

2. Terminology • 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 Introduction to Oracle9i: SQL

3. Types of Functions Introduction to Oracle9i: SQL

4. Case Conversion Functions Alter the case of data stored in a column or character string Introduction to Oracle9i: SQL

5. LOWER Function Used to convert characters to lower-case letters Introduction to Oracle9i: SQL

6. UPPER Function Used to convert characters to upper-case letters Introduction to Oracle9i: SQL

7. INITCAP Function Used to convert characters to mixed-case Introduction to Oracle9i: SQL

8. Character Manipulation Functions Manipulates data by extracting substrings, counting number of characters, replacing strings, etc. Introduction to Oracle9i: SQL

9. SUBSTR Function Used to return a substring, or portion of a string Introduction to Oracle9i: SQL

10. LENGTHFunction Used to determine the number of characters in a string Introduction to Oracle9i: SQL

11. LPADandRPADFunctions Used to pad, or fill in, a character string to a fixed width Introduction to Oracle9i: SQL

12. LTRIMandRTRIMFunctions Used to remove a specific string of characters Introduction to Oracle9i: SQL

13. REPLACEFunction Substitutes a string with another specified string Introduction to Oracle9i: SQL

14. CONCATFunction Used to concatenate two character strings Introduction to Oracle9i: SQL

15. Number Functions Allows for manipulation of numeric data Introduction to Oracle9i: SQL

16. ROUNDFunction Used to round numeric columns to a stated precision Introduction to Oracle9i: SQL

17. TRUNCFunction Used to truncate a numeric value to a specific position Introduction to Oracle9i: SQL

18. Date Functions Used to perform date calculations or format date values Introduction to Oracle9i: SQL

19. MONTHS_BETWEENFunction Determines the number of months between two dates Introduction to Oracle9i: SQL

20. ADD_MONTHSFunction Adds a specified number of months to a date Introduction to Oracle9i: SQL

21. NEXT_DAYFunction Determines the next occurrence of a specified day of the week after a given date Introduction to Oracle9i: SQL

22. TO_DATEFunction Converts various date formats to the internal format (DD-MON-YYYY) used by Oracle9i Introduction to Oracle9i: SQL

23. FormatModelElements - Dates Introduction to Oracle9i: SQL

24. NVL Function Substitutes a value for a NULL value Introduction to Oracle9i: SQL

25. NVL2 Function Allows different actions based on whether a value is NULL Introduction to Oracle9i: SQL

26. TO_CHAR Function Converts dates and numbers to a formatted character string Introduction to Oracle9i: SQL

27. FormatModelElements – TimeandNumber Introduction to Oracle9i: SQL

28. Other Functions • NVL • NVL2 • TO_CHAR • DECODE • SOUNDEX Introduction to Oracle9i: SQL

29. DECODEFunction Determines action based upon values in a list Introduction to Oracle9i: SQL

30. SOUNDEXFunction References phonetic representation of words Introduction to Oracle9i: SQL

31. NestingFunctions • 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 Introduction to Oracle9i: SQL

32. DUALTable • Dummy table • Consists of one column and one row • Can be used for table reference in the FROM clause Introduction to Oracle9i: SQL