SQL CAST() Function: An Overview

The SQL CAST() function converts one data type to another. This article discusses what the function is for, and when and how you’d use it.

In relational databases, every column has a defined data type that controls what kind of data is stored in the column and how that data can be used. For example, a product description is likely to be stored as a string of text, and you couldn’t use it for arithmetic (e.g. calculating totals).

On the other hand, a customer’s balance owing would be stored as numeric data, and it could be used in calculations. SQL has separate sets of functions for numerical operations and for string manipulation.

Occasionally, you may want to override these rules and treat a column in a different way to its data type’s usual operations. This is where the SQL CAST() function becomes useful. It converts a piece of data to a different data type within your query. We’ll look at some examples of when you may need to do this a bit later in the article.

If you’d like to gain a deeper understanding of SQL, look at our SQL From A to Z learning track. This set of 7 courses takes you from beginner all the way through to advanced SQL topics. You’ll gain practical experience through working through more than 700 guided exercises; along the way, you’ll become familiar with the concepts of relational databases. All you need is a browser and an Internet connection, and help is available at every step.

SQL CAST() Function Syntax

The CAST() function has the same simple syntax across all SQL dialects:

CAST(value AS type)

• value can be any valid SQL value. It could be a column name, the result of a calculation, the result of a function, or a literal value.
• type is the desired data type, i.e. what you want the value converted into.

As an example, suppose that the column date_hired in the employee table has been defined as a DATE data type. You want to populate it with today’s date using the GETDATE() function, which returns a value in the DATETIME data type.

You can convert the DATETIME value to a DATE value using this syntax:

INSERT INTO employee (date_hired)
VALUES (CAST(GETDATE() AS DATE));

CAST() Function Examples

Let’s look at a few examples of how CAST() can be used in your daily work.

1.    Using Numbers as Strings

A table named employee has the field department, which is defined as the INT (integer) data type. The first three digits of this code indicate the section of the organization.

Management wants to know how many people are employed in each section. To achieve this, we need to extract the first three digits of the department. We need to convert this INT value to a VARCHAR value, then use the string function LEFT() to retrieve the first three characters.

The query looks like this:

SELECT 
  LEFT(CAST (department AS VARCHAR(15)), 3) AS section, 
  COUNT(*) AS employee_count
FROM employee
GROUP BY LEFT(CAST (department AS VARCHAR(15)), 3)
ORDER BY LEFT(CAST (department AS VARCHAR(15)), 3);

Notice that CAST() is embedded inside the LEFT() function and the functions must be repeated in the GROUP BY and ORDER BY clauses.

2.    Joining Tables on Columns with Different Data Types

The table employee in the previous example needs to be joined to another table department_details, which has the field code defined as a VARCHAR. Ordinarily, joining the department column in employee to the code column in department_details would result in an error, since the fields have different data types. We know that the department code will always be numeric, so we can get around the problem by casting code as an integer.

The query would look like this:

SELECT department_name, name, date_hired
FROM employee
JOIN department_details 
ON department = CAST(code AS int);

3.    Changing Field Types in an ETL Operation

A manufacturing process has sensors at various points which generate an alarm status whenever an unusual condition is encountered. These alarms are automatically loaded into a table named alarms. A daily process copies the rows from alarms into a warehouse table named alarm_history, which can be used for analysis. The alarms table is then cleared.

The alarms table looks like this:

clock_time 		varchar(20),
sensor_no 		int,
status_code 	char(2)

The alarm_history table is similar, but the clock time must be stored as DATETIME, since managers will need it to be extracted and analyzed by a date range. It’s defined like this:

clock_time 		datetime,
sensor_no 		int,
status_code 	char(2)

The query that copies the data to alarm_history needs to convert the VARCHAR clock_time to a DATETIME data type. It looks like this:

INSERT INTO alarm_history
SELECT 
  CAST(clock_time AS DATETIME),
  sensor_no,
  status_code
FROM alarms;

When to Use CAST()

Let’s look at a few scenarios where the SQL CAST() function may be useful.

• ETL (Extract, Transform, Load) Operations: ETL takes data from one or more sources and transforms it into the form needed for a different use. For example, you could take data stored in a system used for a business’s day to day operations and load it into a data warehouse suitable for marketing analysis. The data types in the data warehouse may be different from those in the original system, so CAST() is important here.
• Using functions belonging to a different data type: There may be occasions when you want to use string functions on numeric data – e.g. to format the data in a report. Likewise, you may want to use numeric functions and aggregates on string data (provided you know the column contains numbers).
• Joining tables on columns with different data types: This doesn’t usually happen if the database has been well designed, but in practice it can occur.
• Working with dates stored as strings. Database designers sometimes store dates as text strings; such dates can’t be used for date functions and calculations. You can get around this by using CAST().
• Using dynamic SQL: If you’re writing programs or stored procedures, you may need to incorporate data into a string to build a dynamic query.

If your query returns an error because of a data type mismatch, the CAST() function is probably the solution to your problem.

Data Types Supported by Popular SQL Dialects

1.    SQL Server

SQL Server allows any of its valid data types to be used as the type parameter in the CAST() function. Some conversions are not allowed where the data types are not compatible. You can check which conversions are allowed in the T-SQL documentation.

For full coverage of the CAST() function in SQL Server, see Microsoft’s documentation on the CAST() and CONVERT() functions.

2.    MySQL

MySQL is non-standard in that it doesn’t use the data type for the type parameter. Instead, it has a fixed set of allowed values:

• BINARY
• CHAR (Note: This type is used for any kind of string value, e.g. VARCHAR or TEXT. The database engine will decide on the data type produced depending on the length of the text.)
• DATE
• DATETIME
• DECIMAL
• DOUBLE
• FLOAT
• JSON
• NCHAR
• REAL
• SIGNED
• SPACIAL_TYPE
• UNSIGNED
• YEAR

You can find more information on CAST() in the MYSQL reference manual.

3.    Oracle

Oracle uses the data type for the type parameter. The documentation has a table of data types that are valid for conversion.

4.    PostgreSQL

PostgreSQL uses the data type for the type parameter. The PostgreSQL documentation doesn’t explicitly state what conversions are allowed, but standard conversions between strings, numerics, and dates are fine.

PostgreSQL allows you to create your own casting rules for non-standard conversions using the CREATE_CAST function.

What’s Next with the SQL CAST() Function?

We’ve had a thorough introduction to the SQL CAST() function and how to use it. If you’re looking for a comprehensive study program that covers not only the basics but advanced topics as well – including CAST() – I want to once again recommend our SQL From A to Z track. It’s full of helpful tips, practice exercises, and expert insights.

Good luck and happy learning!