CAST

cast function converts one datatype to another.

To	From
To	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


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));

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>

Home »
Oracle »
Conversion Functions »

CAST: