Oracle SQL Fundamentals/Conversion Functions
This lesson introduces conversion functions.
Objectives and Skills
editObjectives 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
edit- Read Wikipedia: Primitive data type.
- Read Oracle: Conversion Functions.
- Read Oracle: TO_CHAR (character).
- Read Oracle: TO_CHAR (number).
- Read Oracle: TO_CHAR (datetime).
- Read Oracle: TO_NUMBER.
- Read Oracle: TO_DATE.
- Read Oracle: Format Models.
Multimedia
editActivities
edit- Test character conversion functions using HR data.
- Run the following query:
SELECT LAST_NAME, TO_CHAR(SALARY, '$99,999.00') AS SALARY, TO_CHAR(COMMISSION_PCT, 'V99') AS COMMISSION FROM EMPLOYEES;
- Run the following query:
SELECT LAST_NAME, TO_CHAR(HIRE_DATE, 'DY MON DD YYYY') FROM EMPLOYEES;
- Run the following query:
SELECT LAST_NAME, TO_CHAR(HIRE_DATE, 'fmDay, Month D, YYYY') FROM EMPLOYEES;
- Run the following query:
- Test date conversion functions using DUAL.
- Run the following query:
SELECT TO_DATE('012345', 'MMDDYY') AS "DATE" FROM DUAL;
- Run the following query:
SELECT TO_DATE('01/23/45', 'MM/DD/YY') AS "DATE" FROM DUAL;
- Run the following query:
- Test number conversion functions using DUAL.
- Run the following query:
SELECT TO_NUMBER('$12,345.67', '$99,999.00') AS "NUMBER" FROM DUAL;
- Run the following query:
- Test character conversion functions using OE data.
- Select customer credit limit using different numeric formats.
- Select order date using different date formats.
- Test date conversion functions using DUAL.
- Select various character-based date formats and convert the values to dates.
- Test number conversion functions using DUAL.
- Select various character-based numeric formats and convert the values to numbers.
Lesson Summary
edit- 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
edit- Flashcards: Quizlet: Oracle 1Z0-051 Exam - Functions
See Also
editReferences
edit