cast function converts one data type to another.
This Oracle tutorial explains how to use the Oracle/PLSQL CAST function.
The following casts are allowed:
| To | From | ||||||
|---|---|---|---|---|---|---|---|
| BINARY FLOAT / BINARY DOUBLE | CHAR VARCHAR2 | NUMBER | DATE TIMESTAMP INTERVAL | RAW | ROWID UROWID | NCHAR NVARCHAR2 | |
| BINARY FLOAT BINARY DOUBLE | X | X | X | X | |||
| CHAR VARCHAR2 | X | X | X | X | X | X | |
| NUMBER | X | X | X | X | |||
| DATE TIMESTAMP INTERVAL | X | X | |||||
| RAW | X | X | |||||
| ROWID UROWID | X | X | |||||
| NCHAR NVARCHAR2 | X | X | X | X | X | X | |
select CAST( '22-Aug-2015' AS varchar2(30) ) from dual;
The following code converts the date (ie: 22-Aug-2015) into a varchar2(30) value.
The syntax for the Oracle/PLSQL CAST function is:
CAST ( { expr | ( subquery ) | MULTISET ( subquery ) } AS type_name )
-- w w w . j a v a2 s . c o m
SQL> SELECT CAST(12345.67 AS VARCHAR2(10)) from dual;
CAST(12345
----------
12345.67
SQL> select CAST('9A4F' AS RAW(2)) from dual;
CAST
----
9A4F
SQL> select CAST('05-JUL-07' AS DATE) from dual;
CAST('05-
---------
05-JUL-07
SQL> select CAST(12345.678 AS NUMBER(10,2)) from dual;
CAST(12345.678ASNUMBER(10,2))
-----------------------------
12345.68
SQL> select cast( '31-Aug-2012' AS varchar2(30) ) from dual;
CAST('31-AUG-2012'ASVARCHAR2(3
------------------------------
31-Aug-2012
SQL>
Convert column values from one type to another:
CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
SAL NUMBER(7, 2),
DEPTNO NUMBER(2));
-- w w w. j av a2 s. c o m
INSERT INTO EMP VALUES (1, 'SMITH', 'CLERK', 800, 20);
INSERT INTO EMP VALUES (2, 'ALLEN', 'SALESMAN', 1600, 30);
INSERT INTO EMP VALUES (3, 'WARD', 'SALESMAN', 1250, 30);
INSERT INTO EMP VALUES (4, 'JONES', 'MANAGER', 2975, 20);
INSERT INTO EMP VALUES (5, 'MARTIN','SALESMAN', 1250, 30);
INSERT INTO EMP VALUES (6, 'BLAKE', 'MANAGER', 2850, 30);
INSERT INTO EMP VALUES (7, 'CLARK', 'MANAGER', 2850, 10);
INSERT INTO EMP VALUES (8, 'SCOTT', 'ANALYST', 3000, 20);
INSERT INTO EMP VALUES (9, 'KING', 'PRESIDENT',3000, 10);
INSERT INTO EMP VALUES (10,'TURNER','SALESMAN', 1500, 30);
INSERT INTO EMP VALUES (11,'ADAMS', 'CLERK', 1500, 20);
SQL> SELECT CAST(sal AS VARCHAR2(10)) from emp;
CAST(SALAS
----------
800
1600
1250
2975
1250
2850
2850
3000
3000
1500
1500
11 rows selected.
SQL> select CAST(sal + 2 AS NUMBER(7,2)) from emp;
CAST(SAL+2ASNUMBER(7,2))
------------------------
802
1602
1252
2977
1252
2852
2852
3002
3002
1502
1502
11 rows selected.
SQL> select CAST(sal AS BINARY_DOUBLE) from emp;
CAST(SALASBINARY_DOUBLE)
------------------------
8.0E+002
1.6E+003
1.25E+003
2.975E+003
1.25E+003
2.85E+003
2.85E+003
3.0E+003
3.0E+003
1.5E+003
1.5E+003
11 rows selected.
SQL>
Converting a String to a TIMESTAMP WITH LOCAL TIME ZONE
-- w ww . ja va 2s .c om
SQL> SELECT CAST('30-JUN-12' AS TIMESTAMP WITH LOCAL TIME ZONE) FROM dual;
CAST('30-JUN-12'ASTIMESTAMPWITHLOCALTIMEZONE)
---------------------------------------------------------------------------
30-JUN-12 12.00.00.000000 AM
SQL>
SQL> SELECT CAST(TO_TIMESTAMP_TZ('2012-04-21 06:21:31.1234 PST','YYYY-MM-DD HH24:MI:SS.FF TZR') AS TIMESTAMP WITH LOCAL TIME ZONE) FROM dual;
CAST(TO_TIMESTAMP_TZ('2012-04-2106:21:31.1234PST','YYYY-MM-DDHH24:MI:SS.FFT
---------------------------------------------------------------------------
21-APR-12 06.21.31.123400 AM
SQL>
SQL> SELECT CAST(TO_TIMESTAMP_TZ('2012-04-21 06:21:31.1234 EST','YYYY-MM-DD HH24:MI:SS.FF TZR') AS TIMESTAMP WITH LOCAL TIME ZONE) FROM dual;
CAST(TO_TIMESTAMP_TZ('2012-04-2106:21:31.1234EST','YYYY-MM-DDHH24:MI:SS.FFT
---------------------------------------------------------------------------
21-APR-12 03.21.31.123400 AM
SQL>
SQL>