A common task when working with numbers is to format them to a certain amount of decimal places. Two decimal places seems to be the most common format, but we can format to any number of decimal places we want. MySQL provides us with a number of functions that can help us achieve this.

Below are seven functions that can be used to format a number to two decimal places in MySQL.

The ROUND() Function

If the number already has more than two decimal places, we can use the ROUND() function to round it to two decimal places:

SELECT ROUND(1.45678, 2);

Result:

1.46

In this example the number was rounded up, due to the remaining decimal places being higher than 5.

Here’s what happens when the remaining decimal places are lower than 5:

SELECT ROUND(1.45478, 2);

Result:

1.45

This time it wasn’t rounded up.

The TRUNCATE() Function

If we don’t want any rounding to occur, we can use the TRUNCATE() function to truncate the number instead:

SELECT TRUNCATE(1.45678, 2);

Result:

1.45

This example used the same number as the first of the ROUND() examples above. The difference between the two functions can be seen in the result. The ROUND() function returned 1.46 but the TRUNCATE() function returned 1.45.

The CAST() Function

Another way to do it is to convert the number to a decimal type. One function that can do this for us is the CAST() function:

SELECT CAST(543 AS DECIMAL(5, 2));

Result:

543.00

We can use this method even if the number is already a decimal value but with the wrong number of decimal places.

Bear in mind that if you’re reducing the number of decimal places from a number with more than two decimal places, then you could end up with the second decimal place being rounded up:

SELECT CAST(123.4567 AS DECIMAL(5, 2));

Result:

123.46

The CONVERT() Function

Here, we use the CONVERT() function to do the same thing as the CAST() function – convert the number to decimal:

SELECT CONVERT(77, DECIMAL(5, 2));

Result:

77.00

As with CAST(), there’s the possibility of the number being rounded up:

SELECT CONVERT(77.4567, DECIMAL(5, 2));

Result:

77.46

The FORMAT() Function

Another way to format a number to two decimal places is to use the FORMAT() function:

SELECT FORMAT(123456.789, 2);

Result:

123,456.79

The first argument is the number and the second argument is the number of decimal places we want to round the number to.

In this example the decimal part was rounded up. The number also got a comma as the group separator, and a period/full stop as the decimal separator.

This function actually converts the number to a string, so technically, the result is not a numeric type.

The FORMAT() function also accepts a third argument that allows us to specify a locale to use for the result:

SELECT FORMAT(123456.789, 2, 'de_DE');

Result:

123.456,79

Here, I specified 'de_DE' as the locale. This time the group separator is a period/full stop, and the decimal separator is a comma.

The LEFT() Function

We can use the LEFT() function to return a given amount of the leftmost digits:

SELECT LEFT(1.45678, 4);

Result:

1.45

Bear in mind that the LEFT() function isn’t really designed for this kind of thing. The function is intended to be used on strings, and it therefore returns a string. You would also need to know how many digits are in the number in order to return just the right number of decimal places. This technique won’t be suitable in a lot of cases, and it should probably only be used if the other methods aren’t suitable for whatever reason.

Here’s an example of getting the wrong result (i.e. not two decimal places):

SELECT LEFT(123.45678, 4);

Result:

123.

As we can see, we need to know how many digits are in the number, and whether or not it already has a decimal place in order to get the desired result.

The RPAD() Function

The RPAD() function produces a kind of opposite effect to the LEFT() function. Instead of returning a part of what is already there, it adds characters to the right part of the value:

SELECT 
    RPAD(1, 4, '.00') AS "a",
    RPAD(123, 6, '.00') AS "b",
    RPAD(1.5, 4, '0') AS "c",
    RPAD(123, 6, '.45') AS "d";

Result:

+------+--------+------+--------+
| a    | b      | c    | d      |
+------+--------+------+--------+
| 1.00 | 123.00 | 1.50 | 123.45 |
+------+--------+------+--------+

As with the LEFT() function, this function isn’t really intended to do this, but it’s there if you need it.