FB

Oracle CAST FunctionThe Oracle CAST function is quite powerful, but it can be hard to use. Learn how to use it and what it can do in this article.

Purpose of the Oracle CAST Function

The purpose of the Oracle CAST function is to convert one data type to another.

It allows for more functionality than the TO_NUMBER, TO_CHAR and TO_DATE functions, as it allows you to specify the actual data types you want to convert to, rather than just use the defaults of those functions.

Related: How to Convert Data Types in Oracle SQL.

 

Syntax

The syntax for the Oracle CAST function is:

CAST( {expr | MULTISET (subquery) } AS type_name )

This structure is a little different as it does not separate the parameters by a comma, as many other functions do. Instead, it has the word “AS”.

There are two main ways you can run this query. You can run it using a single result data type, which would be:

CAST( expr AS type_name )

Or, you can run it using a subquery, which would be:

CAST( MULTISET (subquery) AS type_name )

 

Parameters

The parameters of the CAST function are:

  • expr (mandatory choice): This is the value or expression that is to be converted to another data type. See below for the valid data types.
  • MULTISET (optional): This keyword indicates that the subquery results will return multiple rows, and are then CAST into a collection value. If this is omitted, and the subquery returns multiple rows, it will only return a single row.
  • subquery (mandatory choice): This is the subquery that can be run to be converted into a collection data type.
  • type_name (mandatory): This is the Oracle data type that the expr or subquery will be converted to. See below for the valid data types.

Get Your Free PDF: 9 Ways to Improve your Database Skills

Oracle SQL Function Cheat Sheet

Subscribe and get a PDF cheat sheet of all of the Oracle SQL functions and parameters for easy reference!

We won't send you spam. Unsubscribe at any time. Powered by ConvertKit

 

Valid Data Types for Oracle Cast

The Oracle CAST function can only convert to and from certain data types. This table from Oracle shows which types are compatible.

from BINARY_FLOAT, BINARY_DOUBLE from CHAR, VARCHAR2 from NUMBER from DATETIME / INTERVAL from RAW from ROWID, UROWID from NCHAR, NVARCHAR2
to BINARY_FLOAT, BINARY_DOUBLE X X X X
to CHAR, VARCHAR2 X X X X X X
to NUMBER X X X X
to DATE, TIMESTAMP, INTERVAL X X
to RAW X X
to ROWID, UROWID X X
to NCHAR, NVARCHAR2 X X X X X X

A few things to note from this table:

  • The Datetime/Interval column includes DATE, TIMESTAMP, TIMESTAMP WITH TIMEZONE, INTERVAL DAY TO SECOND and INTERVAL YEAR TO MONTH.
  • You cannot cast a UROWID to a ROWID if the UROWID contains the value of a ROWID of an index-organized table.
  • CAST does not support any of the LOB data types. If you try to CAST a CLOB into a character data type, for example, the LOB value is converted to a character value and then converted to the target data type. If the resulting value is larger than the target type, then you’ll get an error.

Want to see articles like this on all of the other Oracle functions? Check out this page which lists all of the functions and guides for each of them.

 

Examples of the Oracle CAST Function

Here are some examples of the Oracle CAST function. I find that examples are the best way for me to learn about code, even with the explanation above.

First of all, let’s set up the CUSTOMERS table.

CREATE TABLE customers (
  first_name varchar2(100),
  last_name varchar2(100),
  country varchar2(20),
  full_address CLOB,
  employees number,
  start_date date
);
INSERT INTO customers (first_name, last_name, country, full_address, employees, start_date)
VALUES ('John', 'Smith', 'USA', '10 Long Road', 4, '12-APR-2010');
INSERT INTO customers (first_name, last_name, country, full_address, employees, start_date)
VALUES ('Sally', 'Jones', 'USA', '50 Market Street', 10, '04-JUL-2011');
INSERT INTO customers (first_name, last_name, country, full_address, employees, start_date)
VALUES ('Steve', 'Brown', 'Canada', '201 Flinders Lane', 15, '21-MAR-2009');
INSERT INTO customers (first_name, last_name, country, full_address, employees, start_date)
VALUES ('Mark', 'Allan', 'UK', '8 Smith Street', 23, '1-FEB-2001');
INSERT INTO customers (first_name, last_name, country, full_address, employees, start_date)
VALUES ('Adam', 'Cooper', 'USA', '14 Wellington Road', 55, NULL);
INSERT INTO customers (first_name, last_name, country, full_address, employees, start_date)
VALUES ('Josh', 'Thompson', NULL, '80 Victoria Street', 1, '10-FEB-2012');
INSERT INTO customers (first_name, last_name, country, full_address, employees, start_date)
VALUES ('Peter', 'Manson', 'France', '5 Johnson St', NULL, '16-OCT-2012');
INSERT INTO customers (first_name, last_name, country, full_address, employees, start_date)
VALUES (NULL, '', NULL, '155 Long Road', NULL, '16-OCT-2012');

SELECT *
FROM customers;

Result:

FIRST_NAME LAST_NAME COUNTRY FULL_Address EMPLOYEES START_DATE
John Smith USA 10 Long Road 4 12/APR/10
Sally Jones USA 50 Market Street 10 4/JUL/11
Steve Brown Canada 201 Flinders Lane 15 21/MAR/09
Mark Allan UK 8 Smith Street 23 1/FEB/01
Adam Cooper USA 14 Wellington Road 55 (null)
Josh Thompson  (null) 80 Victoria Street 1 10/FEN/12
Peter Manson France 5 Johnson St  (null) 16/OCT/12
 (null)  (null)  (null) 155 Long Road  (null) 16/OCT/12

We can see the data in the customers table that will be used for a few examples below.

 

Example 1 – Oracle CAST String to DATE

This example shows how to CAST from a string value that contains a date, to a date data type.

SELECT '30-APRIL-2015',
CAST('30-APRIL-2015' AS DATE) AS OUTPUT_VALUE
FROM dual;

Result:

’30-Apr-15′ OUTPUT_VALUE
30-Apr-15 30/APR/15

Notice how the date in the output value is displayed in the date format for Oracle.

 

Example 2 – Oracle CAST NUMBER to String

This example shows how to cast a number to a CHAR data type.

SELECT 41.522,
CAST(41.522 AS CHAR(10)) AS OUTPUT_VALUE
FROM dual;

Result:

41.522 OUTPUT_VALUE
41.522 41.522

It can be hard to see in this example but the alignment is different in the SQL Developer tool, which is done for different data types.

 

Example 3 – Oracle CAST VARCHAR to Number

This example shows how to convert a VARCHAR to a NUMBER.

SELECT '  2093 ',
CAST('  2093 ' AS NUMBER) AS OUTPUT_VALUE
FROM dual;

Result:

‘2093’ OUTPUT_VALUE
   2093 2093

I’ve put spaces around the first number value, which have been removed when it has converted to a NUMBER data type.

 

Example 4 – Oracle CAST DATE to TIMESTAMP

This example shows how to cast a DATE value to a TIMESTAMP value. For this example, I’ve used the CUSTOMERS table shown above.

SELECT start_date,
CAST(start_date AS TIMESTAMP) AS OUTPUT_VALUE
FROM customers;

Result:

START_DATE OUTPUT_VALUE
12/APR/10 12/APR/10 12:00:00.000000000 AM
04/JUL/11 04/JUL/11 12:00:00.000000000 AM
21/MAR/09 21/MAR/09 12:00:00.000000000 AM
01/FEB/01 01/FEB/01 12:00:00.000000000 AM
(null) (null)
10/FEB/12 10/FEB/12 12:00:00.000000000 AM
16/OCT/12 16/OCT/12 12:00:00.000000000 AM
16/OCT/12 16/OCT/12 12:00:00.000000000 AM

Each of the DATE value has been converted to a TIMESTAMP which includes hours, minutes, seconds, and fractional seconds.

 

Example 5 – Oracle Cast NUMBER to VARCHAR2

This example shows how to convert a NUMBER value to a VARCHAR2 data type.

SELECT 9834,
CAST(9834 AS VARCHAR2(30)) AS OUTPUT_VALUE
FROM dual;

Result:

9834 OUTPUT_VALUE
9834 9834

This is similar to Example 2. It can be hard to see in this example but the alignment is different in the SQL Developer tool, which is done for different data types.

 

Example 6 – CAST with Multiset Subquery

This example is a simple example of how to use a multiset subquery with cast.

First, we look at the customers table to find employees in the USA.

SELECT first_name
FROM customers
WHERE country = 'USA';

Result:

FIRST_NAME
John
Sally
Adam

Then, we create a new TYPE to store VARCHAR2 values.

CREATE TYPE usa_employee_first_names AS TABLE OF VARCHAR2(100);

Then, we write our SQL to return a value of that type.

SELECT
CAST(MULTISET(SELECT first_name
  FROM customers
  WHERE country = 'USA')
  AS usa_employee_first_names) AS OUTPUT_VALUE
FROM dual;

 

Result:

OUTPUT_VALUE
SYSTEM.USA_EMPLOYEE_FIRST_NAMES(‘John’,’Sally’,’Adam’)

As you can see, it returns one value. This value is of type “usa_employee_first_names” and includes several values. It can then be used for other purposes, such as inserting into a table.

 

Similar Functions

Some functions in Oracle which are similar to the CAST function are:

  • TO_NUMBER – This function converts a character value to a NUMBER data type.
  • TO_CHAR – This function converts a number or date value to a CHAR data type.
  • TO_DATE – This function converts a character value to a DATE-related data type.

You can find a full list of Oracle functions here.

Get Your Free PDF: 9 Ways to Improve your Database Skills

Oracle SQL Function Cheat Sheet

Subscribe and get a PDF cheat sheet of all of the Oracle SQL functions and parameters for easy reference!

We won't send you spam. Unsubscribe at any time. Powered by ConvertKit

Image courtesy of digitalart / FreeDigitalPhotos.net

Oracle SQL Function Cheat Sheet

Subscribe and get a PDF cheat sheet of all of the Oracle SQL functions and parameters for easy reference!

We won't send you spam. Unsubscribe at any time. Powered by ConvertKit

Get Your FREE PDF: 9 Ways to Improve your Database Skills