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:
In eaxample One output_value is not correct .It gives 30-04-2015 as output
Hi Anil, your output will depend on whatever the default format is for dates on your database. In my database it is DD/MON/YY, but in yours it looks like it is DD-MM-YYYY.
Hope that helps!
how can you convert a number value to text using cast function?
for example the number 123 converted to one hundred twenty three?
Cast will change datatype not the value