Converting a Character to a Number
Retrieving Data with Queries
2-22 Oracle Database 2 Day Developer's Guide
Example 234 Using TO_NUMBER to Convert a Character to a Number
SELECT first_name || ' ' || last_name "Name",
TO_NUMBER('300') + salary "Proposed Salary"
FROM employees
WHERE SUBSTR(job_id, 4, 5) = 'CLERK';
The results of the query appear.
Name Proposed Salary
---------------------------------------------- ---------------
Alexander Khoo 3400
Shelli Baida 3200
Sigal Tobias 3100
...
45 rows selected
Your result set lists all the proposed
salary
values for the selected subset of
employees.
You can use the
TO_DATE
function to convert a character data with a specified format
, you will use the format model '
Month dd,
YYYY, HH:MI A.M.
'; other formats include '
DD-MON-RR
', '
FF-Mon-YY
HH24:MI:SI
', and so on.
Example 235 Using TO_DATE to Convert a Character Data to a Date
SELECT TO_DATE('January 5, 2007, 8:43 A.M.',
'Month dd, YYYY, HH:MI A.M.') "Date"
FROM DUAL;
The results of the query appear.
Date
---------
05-JAN-07
Your result converts the character data, interpreted by the specified format string, into
a
DATE
type.
TO_TIMESTAMP
method with format
models such as '
DD-Mon-RR HH24:MI:SS.FF
'. See Oracle Database SQL Language
Reference for
TO_DATE
.
Example 236 Using TO_TIMESTAMP to Convert Character Data to a Timestamp
SELECT TO_TIMESTAMP('May 5, 2007, 8:43 A.M.',
'Month dd, YYYY, HH:MI A.M.') "Timestamp"
FROM DUAL;
The results of the query appear.
Timestamp
---------------------------------------------
05-MAY-07 08.43.00.000000000 AM
Your result converts the character data, interpreted by the specified format string, into
a
TIMESTAMP
type.
