CAST
MySQL
ORACLE
POSTGRESQL
SQL
SQL SERVER

SQL CAST Function: Everything You Need to Know

intro

Explore the CAST function offered by all database management systems that follow the SQL standard and become a data type conversion wizard.

Tools used in the tutorial
Tool Description Link
Dbvisualizer DBVISUALIZER
TOP RATED DATABASE MANAGEMENT TOOL AND SQL CLIENT
MySQL logo MySQL
THE MYSQL DATABASE

Casting data from one type to another is a common operation when dealing with a database. The reason is that data conversion underlies several useful operations, such as calculations, function calls, and comparisons. That is why the ANSI standard specifies the SQL CAST function to convert a value from one data type to another.

In this article, you will learn what the CAST SQL function is, how it works, and how to use it in real-world scenarios.

Let's dive in!

What Is CAST in SQL?

CAST in SQL is a function to explicitly convert a value of one data type to another. As part of the SQL standard specification, it is available with the same syntax in different DBMS systems, including MySQL, SQL Server, PostgreSQL, and Oracle. Since database management systems differ, the way CAST is implemented and works under the scenes changes from database to database.

The SQL CAST function accepts a value of any type and attempts to convert it into the specified destination data type. Since not all data conversions are possible, it may raise an error or produce unexpected results during this operation. Refer to the data conversion table of your specific database for more details:

SQL data type casting is useful to facilitate comparisons, arithmetic operations, or to comply with particular data type requirements. Let’s now see how to use the CAST SQL function!

CAST SQL Function: Syntax and First Examples

The SQL CAST function has the following syntax:

Copy
        
1 CAST(expression AS target_data_type)

Where:

  • expression is the value you want to convert to the target_data_type type.
  • target_data_type the destination SQL data type expression should be cast to.

Note: target_data_type must be one of the target data types supported by your DBMS. This means that you may not be able to use just any data type. For example, MySQL only supports DATE, DATETIME, DECIMAL, TIME, CHAR, NCHAR, SIGNED, UNSIGNED, and BINARY as target cast data types.

Now, suppose you want to convert the string “123” to an INT. This is how you can with this CAST SQL Server query:

Copy
        
1 SELECT CAST('123' AS INT);

The result will be the number 123

When expression is NULL, the result will be NULL as well.

Keep in mind that CAST is not the only function you have to convert data from one type to another. Depending on the SQL dialect you are using, you may also encounter alternative functions and operators like expression::target_data_type or CONVERT(data_type, expression). Also, do not forget that most DBMS technologies convert implicit type conversions under the hood when necessary.

SQL CAST In Action

Time to see CAST in action in some real-world use cases!

Since it is not always easy to figure out the data type of an SQL expression, we will use DbVisualizer in this section. This powerful database client enables you to get the data type of an SQL expression simply by clicking on a cell. That nice feature simplifies data conversion a lot!

Consider the following example. 123 could be either a string or a number, but thanks to the indication in the lower left corner you can tell it is a number:

Note the “Number: Unformatted” indication
Note the u201cNumber: Unformattedu201d indication

Now that you are aware of this feat, explore some SQL CAST data conversion examples!

The following queries will be relevant to MySQL, but you can easily adapt them to any other DBMS.

Float to Integer

You have the decimal number 42.69 and you want to convert it to its integer representation. Here is how you can do it:

Copy
        
1 SELECT CAST(42.69 AS SIGNED);

In MySQL, the result will be:

Copy
        
1 43

Note that how the decimal-to-integer conversion occurs depends on the database technology you are using. Some round to the nearest integer, while others take only the integer part.

Integer to Float

When dealing with arithmetic calculations, it may be necessary to convert an integer to a decimal to obtain the desired results without truncation. Consider the cast as decimal SQL example below:

Copy
        
1 SELECT CAST(3 AS DECIMAL);

The result will be the decimal representation of 3 :

As shown by DbVisualizer, that 3 is a decimal number
As shown by DbVisualizer, that 3 is a decimal number

String to Date

Another common scenario is to convert a string to a date. You can achieve that with the SQL cast as date example below:

Copy
        
1 SELECT CAST('2024-12-21' AS DATE);

The result will be the DATE:

Copy
        
1 2024-12-21
The reusling string is a date in the “yyyy-MM-dd” format
The reusling string is a date in the u201cyyyy-MM-ddu201d format

If you write an invalid date string, the result will be

Copy
        
1 NULL

in MySQL:

Note how an invalid date strings lead to a NULL result
Note how an invalid date strings lead to a NULL result

Keep in mind that databases usually offer specific dates for string-to-date and date-to-string conversions. These offer more options and typically represent a better alternative than the CAST SQL function. For more info, read our guide on date formatting in Postgres.

Integer to String

DBMS systems generally offer a lot of functions and operators for string data types. This is why you may need to cast an integer to a string in SQL. Achieve that with the following MySQL query:

Copy
        
1 SELECT CAST(42 AS CHAR)

The result will be the text representation of 42 :

“42” is text
u201c42u201d is text

Awesome! You are now a CAST SQL expert!

SQL CAST Function: Best Practices

Here is a list of best practices for the CAST function in SQL:

  • Convert data only when required as cast operations can lead to overhead, data loss, and inaccurate results.
  • Consult the documentation to learn which data types can be converted between them and which cannot.
  • Use a database client such as DbVisualizer to verify that the data has been converted to the desired type with a single click.
  • Use specific cast date SQL functions for more control over the result.
  • Pay attention to automatic rounding when converting from float to integer to avoid data truncation.

Conclusion

In this guide, you understood what the CAST function in SQL is and how it works. You now know that CAST is a standard data type conversion function available in most DBMS technologies. Thanks to the usage examples shown here, you have also learned how and when to use it.

Figuring out the type of an SQL value is not always easy. Thanks to a powerful database client like DbVisualizer, that becomes a piece of cake! With a single click on a cell, you can get instant feedback on the data type of the value stored in it. This is just one of the many features supported by this powerful tool, which also includes advanced query optimization capabilities and automatic ERD-like schema generation. Try DbVisualizer for free today!

FAQ

Is the SQL CAST function part of the standard SQL specification?

Yes, the SQL CAST function is part of the standard SQL specification. It provides a consistent method for converting data types across different database systems, ensuring portability and interoperability. However, specific implementations may slightly vary in implementation and behavior.

What DBMS technologies support CAST?

Most major database management systems support the CAST function, including MySQL, PostgreSQL, SQL Server, Oracle, and SQLite.

Can you cast a type to all other types in SQL?

No, you cannot cast a data type to all other types in SQL. Data type conversions are limited to compatible types defined by the database system. Attempting to cast a type to an incompatible or unsupported type may result in errors or produce unexpected results. Read the documentation of your specific database system for information on supported data type casting.

What is the difference between CAST and the :: operator in PostgreSQL?

In PostgreSQL, both CAST and the :: operator are used for data type conversion. However, CAST is SQL-standard compliant and provides more readability and portability across different database systems. Instead, the :: operator is a PostgreSQL-specific shorthand for type casting, commonly used for query readability.

What is the difference between the SQL CAST function and the SQL CONVERT function?

Both CAST and CONVERT are SQL functions to convert data types, but they differ in syntax and usage. CAST is standard SQL supported across database systems, while CONVERT is database-specific. Refer to the documentation of the SQL dialect you are using for more details on their availability.

Dbvis download link img
About the author
Antonello Zanini

Antonello is a software engineer, and often refers to himself as a technology bishop. His mission is to spread knowledge through writing.

The Table Icon
Sign up to receive The Table's roundup
More from the table
Title Author Tags Length Published
title

Glossary of the SQL Commands You Need to Know

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 12 min 2024-04-11
title

SUBSTRING_INDEX in SQL Explained: A Guide

author Lukas Vileikis tags MySQL SQL 8 min 2024-04-08
title

SQL NOT IN: the Good, Bad & the Ugly

author Lukas Vileikis tags MySQL SQL 7 min 2024-04-04
title

SQL Add to Date Operations: A Complete Guide

author Antonello Zanini tags DATE DATETIME MySQL ORACLE POSTGRESQL SQL SQL SERVER 6 min 2024-04-01
title

PostgreSQL Upsert: INSERT ON CONFLICT Guide

author Antonello Zanini tags POSTGRESQL UPSERT 7 min 2024-03-25
title

INSERT INTO SQL Clause

author Leslie S. Gyamfi tags INSERT SQL 6 min 2024-03-18
title

Postgres TEXT vs VARCHAR: Comparing String Data Types

author Antonello Zanini tags POSTGRESQL TEXT VARCHAR 6 min 2024-03-14
title

ALTER TABLE ADD COLUMN in SQL: A Comprehensive Guide

author TheTable tags SQL 5 min 2024-03-12
title

Schemas in PostgreSQL

author Leslie S. Gyamfi tags POSTGRESQL SCHEMA 6 min 2024-03-11
title

SQL Server for Mac: The Ultimate Guide to the Best SQL Server Client

author TheTable tags SQL SERVER 7 min 2024-03-01

The content provided on dbvis.com/thetable, including but not limited to code and examples, is intended for educational and informational purposes only. We do not make any warranties or representations of any kind. Read more here.

Cookie policy

We use cookies to ensure that we give you the best experience on our website. However you can change your cookie settings at any time in your browser settings. Please find our cookie policy here ↗