SQL CAST Function Explained with Examples

The SQL 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.

 

This article applies to Oracle, SQL Server, MySQL, and Postgres.

Purpose of the SQL CAST Function

The purpose of the SQL 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 in Oracle, and other specific data type 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 SQL CAST function is:

CAST( expression AS type_name )

In Oracle, you can also use it with a subquery type:

CAST( MULTISET (subquery) AS type_name )

We’ll see examples of each of these later in the article.

 

Parameters

The parameters of the CAST function are:

• expression (mandatory choice): This is the value or expression that is to be converted to another data type. See below for the valid data types.
• type_name (mandatory): This is the SQL data type that the expression will be converted to. See below for the valid data types

In Oracle’s multiset syntax:

• 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.

 

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.

While we’re here, if you want an easy-to-use PDF guide for the main features in different database vendors, get my SQL Cheat Sheets here:

 

Examples of the SQL CAST Function

Here are some examples of the SQL 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 varchar(100),
  last_name varchar(100),
  country varchar(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/FEB/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 – SQL CAST String to DATE

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

SELECT CAST('30-APRIL-2022' AS DATE);

Result:

 30/APR/22

This will display the value in a Date format.

 

Example 2 – SQL CAST NUMBER to String

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

SELECT CAST(41.522 AS CHAR(10));

Result:

41.522

It can be hard to see in this example but the alignment is different in your IDE, 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 CAST('  2093 ' AS NUMBER)l;

Result:

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 – SQL 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 values has been converted to a TIMESTAMP which includes hours, minutes, seconds, and fractional seconds.

 

Example 5 – SQL Cast NUMBER to VARCHAR

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

SELECT CAST(9834 AS VARCHAR(30)) AS OUTPUT_VALUE;

Result:

9834

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

 

Example 6 – CAST with Multiset Subquery (Oracle)

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.

While we’re here, if you want an easy-to-use PDF guide for the main features in different database vendors, get my SQL Cheat Sheets here: