Data Manipulation   «Prev  Next»

Lesson 1

Manipulating Data using Functions in Oracle

When using data from the database, you may find that you want to manipulate the data to fit your needs. For example, you may want to combine the city, state, and zip code together when printing out a mailing label. This module covers several built-in SQL functions that you have seen before but that have a slightly different syntax when done in Oracle. This module also covers functions that are exclusive Oracle extensions to SQL.

Module Objectives

By the end of this module, you will be able to:
  1. List character (string) functions unique to Oracle or different in Oracle
  2. Interpret the effect of number functions
  3. Identify valid date-related date functions and Oracle's standard date format
  4. Identify valid time-related date/time functions
  5. Use DECODE and NVL to modify query results
The module looks at functions by focusing on functions for character fields, for number fields, and for date/time fields.
The next several lessons discuss some functions that operate on character strings.

Oracle Functions manipulate data and return a result. In addition to an alphabetical listing of all functions, this chapter contains an overview of functions including:
  1. Numeric functions
  2. Character functions returning character values
  3. Character functions returning number values
  4. String functions
  5. LOB functions
  6. NLS character set functions
  7. General comparison functions
  8. Null-related comparison functions
  9. Conversion functions
  10. Datetime functions
  11. Aggregate functions
  12. Analytic functions
  13. Encoding function
  14. User and session functions
  15. Cache grid functions

Numeric Functions

Numeric functions accept numeric input and return numeric values. The numeric functions are:
  1. ABS
  2. CEIL
  3. FLOOR
  4. MOD
  5. POWER
  6. ROUND (expression)
  7. SIGN
  8. SQRT
  9. TRUNC (expression)


Character functions returning character values

The character functions that return character values are:
  1. CHR
  2. CONCAT
  3. LOWER and UPPER
  4. LPAD
  5. LTRIM
  6. NCHR
  7. NLSSORT
  8. REPLACE
  9. RPAD
  10. RTRIM
  11. SOUNDEX
  12. SUBSTR, SUBSTRB, SUBSTR4
  13. TRIM

Character Functions returning number values

Character functions that return number values are:
  1. ASCIISTR
  2. INSTR, INSTRB, INSTR4
  3. LENGTH, LENGTHB, LENGTH4

String functions

TimesTen supports these string functions in SELECT statements:
  1. INSTR, INSTRB, INSTR4
  2. LENGTH, LENGTHB, LENGTH4
  3. SUBSTR, SUBSTRB, SUBSTR4
A selected value that specifies a string function causes the SELECT result to be materialized. This causes overhead in both time and space.
A function is a built-in PL/SQL program that always returns a single value. You can use the predefined functions or you can create your own. A function always returns a single value, as opposed to a procedure, which is a similar type of program but is able to return more than one value. You can call a function within a query or other SQL command. Use the NVL function to convert null values before comparing them.

SEMrush Software TargetSEMrush Software Banner