How to Cast to Decimal in MySQL

1. Use CAST() With the DECIMAL(M,D) Function to Cast to Decimal in MySQL
2. Use CONVERT() With the DECIMAL(M,D) Function to Cast to Decimal in MySQL

Sometimes, we may need to cast one data type to another. Here is how we can cast to decimal in MySQL using the CAST() and CONVERT() functions with DECIMAL(M,D).

Use CAST() With the DECIMAL(M,D) Function to Cast to Decimal in MySQL

We can use the CAST() function to convert one data type to another. It is often used with HAVING, WHERE, and JOIN clauses.

Example code:

SELECT CAST(15 AS DECIMAL(4,2)) AS Decimal_Value;

Output:

+---------------+
| Decimal_Value |
+---------------+
|        15.00  |
+---------------+
1 row in set (0.00 sec)

We converted from an int into a decimal considering the query given above. Remember, we must specify the precision and scale for converting to the decimal data type.

We used the DECIMAL(M,D) function, which takes two parameters. The first parameter denotes precision, and the second represents the scale.

The precision denotes the significant number of digits, while the scale shows the digits’ number that we can have (store) after the decimal point.

In the above example, 4 is the precision and 2 is the scale. We can also mention the column name instead of writing 15.

According to the MySQL documentation, M must be more than or equal to D while using FLOAT(M,D), DECIMAL(M,D), or DOUBLE(M,D). For instance, the following query’s M is 2, and D is 4, which does not satisfy the condition M>=D and generates an error.

SELECT CAST(15 AS DECIMAL(2,4)) AS Decimal_Value;

Always remember that the syntax of DECIMAL(M,0) is equivalent to the DECIMAL(M). Similarly, the decimal syntax is equivalent to DECIMAL(M) and DECIMAL(M,0), where the default value of M is 10.

All of the following queries produce the same output.

Example code:

SELECT CAST(15 AS DECIMAL(4,0)) AS Decimal_Value;
SELECT CAST(15 AS DECIMAL(4)) AS Decimal_Value;
SELECT CAST(15 AS DECIMAL) AS Decimal_Value;

Output (for all three queries given above):

+---------------+
| Decimal_Value |
+---------------+
|            15 |
+---------------+
1 row in set (0.00 sec)

Use CONVERT() With the DECIMAL(M,D) Function to Cast to Decimal in MySQL

We can also use CONVERT() with the DECIMAL(M,D) function to cast from int to decimal. This is similar to the CAST() function discussed above.

Example code:

SELECT CONVERT(15, DECIMAL(4,2)) AS Decimal_Value;

Output:

+---------------+
| Decimal_Value |
+---------------+
|         15.00 |
+---------------+
1 row in set (0.00 sec)