MySQL provides DECIMAL, CAST(), and CONVERT() to accurately handle numeric values and safely convert data between different types. These features are critical when working with prices, calculations, and formatted data.
DECIMAL is commonly used for financial values where accuracy matters. CAST and CONVERT are used to change one data type into another during queries.
-- DECIMAL datatype syntax
DECIMAL(total_digits, decimal_places)
-- CAST syntax
CAST(expression AS datatype)
-- CONVERT syntax
CONVERT(expression, datatype)
-- Creating a table using DECIMAL for price
CREATE TABLE products (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50),
price DECIMAL(10,2)
);
-- Casting a string value to DECIMAL
SELECT CAST('123.45' AS DECIMAL(5,2)) AS converted_price;
-- Converting integer to DECIMAL using CONVERT
SELECT CONVERT(100, DECIMAL(6,2)) AS decimal_value;
The DECIMAL datatype stores numbers exactly as defined. CAST() and CONVERT() change the data type at runtime without modifying table structure. This is useful in calculations, reports, and formatted output.
Enter a raw number and choose how many decimal places to cast it to.