How to use CAST() function in MySQL
Key takeaways:
The
CAST()function allows for changing the data type of an expression, facilitating type casting in SQL queriesThe syntax of
CAST()function isCAST(expression AS data_type), with an optional length parameter for precision.
CAST()can be applied to column values, constants, function results, mathematical expressions, string literals, and subqueries.The
CAST()function is essential for ensuring data integrity and compatibility in SQL queries.
MySQL is a relational database management system developed by Oracle Corporation. It is widely used for managing databases due to its speed, reliability, and ease of use. Maintaining data integrity and handling data efficiently are key priorities in a database management system.
A common challenge occurs when data stored in different formats or types needs to be processed uniformly. This is where MySQL’s CAST() function becomes especially useful.
The CAST() function
The CAST() function is used to change the
This function is mostly used when explicit data conversion from one type to another is required within a SQL query and this process is usually known as type casting.
Use the CAST() function only when required because it can lead to performance overhead because of additional processing. It can also cause data loss during data type conversion, and it may yield inaccurate results due to improper use of CAST() function.
Syntax
The function CAST() is a standard function in SQL. We can use the AS keyword within the CAST() function as follows:
SELECT CAST(expression AS new_data_type[(length)]);
The syntax for the CAST() function usually depends on how it is being used. The length parameter is optional and specifies the length or precision of the resulting data type.
MySQL supports CAST() from version 4.0.2 onward.
Have a look at the syntax for column values, constants, results of functions, and more in this section.
Column values: We can cast the values of a specific column in a table.
SELECT CAST(column_name AS new_data_type[(length)])FROM table_name;
Constants: We can cast constant values directly.
SELECT CAST('123' AS INTEGER);
Results of functions: We can cast the results returned by functions.
SELECT CAST(SUM(column_name) AS DECIMAL[(length)])FROM table_name;
Mathematical expressions: We can cast the result of a mathematical expression.
SELECT CAST(3.14 * 2 AS INTEGER);
String literals: We can cast string literals to different data types.
SELECT CAST('2024-07-01' AS DATE);
Subqueries: We can cast the result of a subquery.
SELECT CAST((SELECT MAX(column_name) FROM table_name) AS CHAR[(length)]);
Example
Let’s discuss the examples for casting in MySQL. We can consider an example of the Products table that contains the information about the products.
Products
ProductID | ProductName | Category | Price | UnitsSold |
1 | Smartphone | Electronics | 599.49 | 100 |
2 | Laptop | Electronics | 999.19 | 50 |
3 | Headphones | Electronics | 149.49 | 75 |
4 | T-shirt | Apparel | 19.94 | 200 |
5 | Jeans | Apparel | 39.76 | 150 |
6 | Running shoes | Footwear | 79.83 | 100 |
7 | Bagpack | Accessories | 49.92 | 80 |
Click the ”Run” button to see the table’s content:
SELECT * FROM Products;
Let’s discuss how typecasting can be done in MySQL.
Casting a column value: We can typecast the values of a specific column in a table using the SQL query.
SELECT CAST(Price AS UNSIGNED) AS outputFROM Products;
Casting a constant: We can typecast the constant values directly.
SELECT CAST('12345' AS UNSIGNED) AS output;
Casting a function result: We can typecast the results returned by functions. Let's take an average of Price column and convert the data type of the return value to DECIMAL. Here, (10, 2) refers to the total length of the resulting data type, which is 10 digits, including 2 decimal places.
SELECT CAST(AVG(Price) AS DECIMAL(10, 2)) AS outputFROM Products;
Casting a mathematical expression: We can typecast the result of a mathematical expression as follows:
SELECT CAST(3.14 * 2 AS SIGNED) AS output;
Casting a string literal: We can typecast string literals to different data types.
SELECT CAST('2024-07-01' AS DATE) AS output;
A query in MySQL returns NULL in case of an invalid input. Let’s check by adding an invalid value like 2024-70-70.
Casting a subquery result: We can typecast the result of a subquery.
SELECT CAST((SELECT MAX(Price) FROM Products) AS CHAR) AS output;
Conclusion
The CAST() function is a fundamental MySQL feature used to convert data from one type to another. Its syntax, CAST(expression AS data_type), is consistent across various DBMSs, though minor differences exist in implementation.
It enables seamless data type conversions, ensuring compatibility and accuracy in SQL queries.
Free Resources