Convert Floats to Integers in MySQL

In MySQL, you can convert floating-point numbers to integers using the CAST() or CONVERT() functions. These functions truncate the decimal portion of a float and return only the integer part.

In this tutorial, you will learn how to convert floats to integers using CAST() and CONVERT(), with examples that show how to apply these functions effectively in MySQL.

Converting Floats to Integers with CAST and CONVERT

When converting floats to integers in MySQL, CAST() and CONVERT() are commonly used functions. Both functions remove the decimal portion of a float by truncating it, which can be useful for rounding down values.

Examples: Converting Floats to Integers

We’ll go through examples that illustrate how to convert float values to integers using CAST() and CONVERT(). In these examples, we’ll convert sample float values directly without using tables.

1. Using CAST() to Convert Floats to Integers

To convert a float like 123.456 to an integer using CAST():

</>
Copy
SELECT CAST(123.456 AS UNSIGNED) AS integer_value;

This query returns 123, as CAST() removes the decimal portion, returning only the integer part.

Convert Floats to Integers in MySQL - Example 1: convert 123.456 to integer

2. Using CONVERT() to Convert Floats to Integers

To convert a float, such as 78.912, to an integer using CONVERT():

</>
Copy
SELECT CONVERT(78.912, UNSIGNED) AS integer_value;

This query returns 78, as CONVERT() also truncates the decimal, leaving only the integer portion.

Convert Floats to Integers in MySQL - Example 2 using CONVERT() function

3. Converting Column Values from Float to Integer

If you have a prices table with a column price containing float values, you can convert these to integers as follows:

</>
Copy
SELECT price, CAST(price AS UNSIGNED) AS integer_price
FROM prices;

This query selects the original price and an integer version of it, allowing you to compare both values side by side.

Using ROUND() for Rounding Before Conversion

To round a float before converting it to an integer, use the ROUND() function in combination with CAST() or CONVERT():

</>
Copy
SELECT CAST(ROUND(123.789) AS UNSIGNED) AS rounded_integer_value;

This query rounds 123.789 to 124 before converting it to an integer, producing a rounded integer result.

Convert Floats to Integers in MySQL - Example: Using ROUND() for Rounding Before Conversion