SQL CAST Function

SQL CAST Function is used for explicitly converting the information from one data-type to another data-type.

The SQL CAST does not allow us to specify the format of the result whereas the SQL Convert function allows us to do the same.

SQL CAST is part of the SQL – 92 specifications; the CAST function in SQL is used whenever we want the SQL to be portable between the databases.


SQL CAST Function Syntax

CAST ( { expression | ( subquery ) | MULTISET ( subquery ) } AS data-type )

Where,
expression or subquery is any value or expression that we want to convert.
data-type is the new data-type.

The table below shows the allowed conversions by using SQL CAST:

FROM

TO

char, varchar2 number datetime / interval raw rowid, urowid nchar, nvarchar2
char, varchar2 No No No No No Yes
Number No No Yes Yes Yes No
datetime / interval No Yes No Yes Yes No
raw No Yes Yes No Yes No
rowid, urowid No Yes Yes Yes No No
nchar, nvarchar2 Yes Yes Yes Yes Yes No

Let’s see some examples for understanding SQL CAST.

SQL CAST Example – Cast NUMBER to VARCHAR2(10) Type

Suppose we have a table named ‘employee’ as shown below

Employee_ID Employee_Name Salary Department_ID Commission
101 Emp A

10000

1000
102 Emp B

20000

2000 20
103 Emp C

28000

2000 20
104 Emp D

30000

3000  5
105 Emp E

32000

1000 10
106 Emp F

32000

1000 5
108 Emp H

12000

3000

Now suppose we want the salary of employees to be retrieved in VARCHAR2(10) format from the ‘employee’ table

We can achieve the same by using SQL CAST in SQL SELECT Statement as:

select CAST(salary AS VARCHAR2(10)) SALARY_OF_EMPLOYEE
from employee;

The result of the above query will be:

Salary_OF_EMPLOYEE
10000
20000
28000
30000
32000
32000
12000

Here, using SQL CAST Function, we have retrieved salaries of all employees from the ‘employee’ table in VARCHAR2 format.


SQL CAST Example – Cast Date to VARCHAR2 Type

SELECT CAST(’01-Jan-2013’ AS VARCHAR2(50))
FROM dual;

The above SQL SELECT Statement with SQL CAST will convert the date ’01-Jan-2013’ into a VARCHAR2(50) value.

Hope above information on SQL CAST was helpful to you; please share the same with others also.


Tagged , , , , , , , , , . Bookmark the permalink.