< Oracle SQL Fundamentals

This lesson introduces conversion functions.

Objectives and Skills

Objectives and skills for the conversion functions portion of Oracle SQL Fundamentals I certification include:[1]

  • Using Conversion Functions and Conditional Expressions
    • Describe various types of conversion functions that are available in SQL
    • Use the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions

Readings

  1. Read Wikipedia: Primitive data type.
  2. Read Oracle: Conversion Functions.
  3. Read Oracle: TO_CHAR (character).
  4. Read Oracle: TO_CHAR (number).
  5. Read Oracle: TO_CHAR (datetime).
  6. Read Oracle: TO_NUMBER.
  7. Read Oracle: TO_DATE.
  8. Read Oracle: Format Models.

Multimedia

  1. YouTube: Oracle SQL Tutorial - Querying a table - Part 10(e) - Single-Row Conversion Functions

Activities

  1. Test character conversion functions using HR data.
    1. Run the following query:
      SELECT LAST_NAME, TO_CHAR(SALARY, '$99,999.00') AS SALARY, TO_CHAR(COMMISSION_PCT, 'V99') AS COMMISSION FROM EMPLOYEES;
    2. Run the following query:
      SELECT LAST_NAME, TO_CHAR(HIRE_DATE, 'DY MON DD YYYY') FROM EMPLOYEES;
    3. Run the following query:
      SELECT LAST_NAME, TO_CHAR(HIRE_DATE, 'fmDay, Month D, YYYY') FROM EMPLOYEES;
  2. Test date conversion functions using DUAL.
    1. Run the following query:
      SELECT TO_DATE('012345', 'MMDDYY') AS "DATE" FROM DUAL;
    2. Run the following query:
      SELECT TO_DATE('01/23/45', 'MM/DD/YY') AS "DATE" FROM DUAL;
  3. Test number conversion functions using DUAL.
    1. Run the following query:
      SELECT TO_NUMBER('$12,345.67', '$99,999.00') AS "NUMBER" FROM DUAL;
  4. Test character conversion functions using OE data.
    1. Select customer credit limit using different numeric formats.
    2. Select order date using different date formats.
  5. Test date conversion functions using DUAL.
    1. Select various character-based date formats and convert the values to dates.
  6. Test number conversion functions using DUAL.
    1. Select various character-based numeric formats and convert the values to numbers.

Lesson Summary

  • TO_CHAR (character) converts NCHAR, NVARCHAR2, CLOB, or NCLOB data to the database character set. The value returned is always VARCHAR2.[2]
  • TO_CHAR (n, [format]) converts n to a value of VARCHAR2 data type, using the optional number format.[3]
  • TO_CHAR (datetime, [format]) converts a datetime or interval value of DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, INTERVAL DAY TO SECOND, or INTERVAL YEAR TO MONTH data type to a value of VARCHAR2 data type in the format specified by the date format.[4]
  • TO_NUMBER(expression, [format]) converts expression to a value of NUMBER data type. The expression can be a number value of CHAR, VARCHAR2, NCHAR, NVARCHAR2, BINARY_FLOAT, or BINARY_DOUBLE data type.[5]
  • TO_DATE(char, [format]) converts char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type to a value of DATE data type.[6]

Assessments

See Also

References

This article is issued from Wikiversity. The text is licensed under Creative Commons - Attribution - Sharealike. Additional terms may apply for the media files.