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:
1
CAST(expression AS target_data_type)
Where:
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:
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:
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:
1
SELECT CAST(42.69 AS SIGNED);
In MySQL, the result will be:
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:
1
SELECT CAST(3 AS DECIMAL);
The result will be the decimal representation of 3 :
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:
1
SELECT CAST('2024-12-21' AS DATE);
The result will be the DATE:
1
2024-12-21
If you write an invalid date string, the result will be
1
NULL
in MySQL:
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:
1
SELECT CAST(42 AS CHAR)
The result will be the text representation of 42 :
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:
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.
