Chapter 5 Selected Single-Row Functions

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

2. Chapter Objectives • Use the UPPER, LOWER, and INITCAP functions to change the case of field values and character strings • Extract a substring using the SUBSTR function • Determine the length of a character string using the LENGTH function Oracle9i: SQL

3. Chapter Objectives • Use the LPAD and RPAD functions to pad a string to a desired width • Use the LTRIM and RTRIM functions to remove specific characters strings • Round and truncate numeric data using the ROUND and TRUNC functions • Calculate the number of months between two dates using the MONTHS_BETWEEN function Oracle9i: SQL

4. Chapter Objectives • Identify and correct problems associated with calculations involving null values using the NVL function • Display dates and numbers in a specific format with the TO_CHAR function • Determine the current date setting using the SYSDATE keyword • Nest functions inside other functions • Identify when to use the DUAL table Oracle9i: SQL

5. 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 Oracle9i: SQL

6. Types of Functions Oracle9i: SQL

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

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

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

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

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

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

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

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

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

16. REPLACEFunction Substitutes a string with another specified string Oracle9i: SQL

17. CONCATFunction Used to concatenate two character strings Oracle9i: SQL

18. Number Functions Allows for manipulation of numeric data Oracle9i: SQL

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

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

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

22. MONTHS_BETWEENFunction Determines the number of months between two dates Oracle9i: SQL

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

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

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

26. FormatModelElements - Dates Oracle9i: SQL

27. NVL Function Substitutes a value for a NULL value Oracle9i: SQL

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

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

30. FormatModelElements – TimeandNumber Oracle9i: SQL

31. Other Functions • NVL • NVL2 • TO_CHAR • DECODE • SOUNDEX Oracle9i: SQL

32. DECODEFunction Determines action based upon values in a list Oracle9i: SQL

33. SOUNDEXFunction References phonetic representation of words Oracle9i: SQL

34. 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 Oracle9i: SQL

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