JIYIK CN >

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

Using CAST() with the DECIMAL(M,D) function to convert to decimal in MySQL

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

Sample code:

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

Output:

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

Considering the query given above, we are converting int to decimal. Remember, we have to specify the precision and scale for conversion to decimal data type.

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

Precision means the number of significant digits, while scale means the number of digits 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, when using FLOAT(M,D), DECIMAL(M,D) or DOUBLE(M,D), M must be greater than or equal to D. For example, in the following query, M is 2 and D is 4, which does not meet the condition M>=D and an error is reported.

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

Always remember that the syntax of DECIMAL(M,0) is equivalent to DECIMAL(M). Likewise, 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.

Sample code:

mysql> SELECT CAST(15 AS DECIMAL(4,0)) AS Decimal_Value;
mysql> SELECT CAST(15 AS DECIMAL(4)) AS Decimal_Value;
mysql> 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 convert to decimal in MySQL

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

Sample code:

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

Output:

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