SQL Query to Remove Decimal Values

In SQL, decimal values are often used to represent floating-point numbers. However, there are instances where we might need to remove decimal places or adjust the number of decimals in your queries.

In this article explains various methods in SQL to remove or round off decimal values, including ROUND(), FLOOR(), and CAST(), providing clear examples for each method.

How to Remove Decimal Values in SQL

There are multiple ways to remove decimal values in SQL, depending on your needs. These functions help us manage and control the decimal precision:

1. Using the ROUND() Function
2. Using the FLOOR() Function
3. Using the CAST() Function

To demonstrate these methods, let’s assume we have the following geeksforgeeks table:

1. Using the ROUND() Function

The ROUND() function in SQL is one of the most common ways to handle decimal values. This function rounds a number to the specified number of decimal places. If we wish to remove all decimals and round the value to the nearest integer, we can use 0 as the number of decimal places.

Syntax:

ROUND(Value, decimal_place)

Example

In this example, round the values in the MARKS column to 0 decimal places (removing the decimals):

SELECT NAME, ROUND(MARKS,0) AS MARKS FROM geeksforgeeks;

Output

Explanation:

• 80.9 is rounded to 81, as it’s closer to 81 than 80.
  
• 86.89 becomes 87, as 87 is the nearest integer.
  
• Similarly, all decimal values are rounded to the nearest whole number.

2. Using the FLOOR() Function

The FLOOR() function in SQL returns the largest integer less than or equal to a given value. Unlike the ROUND() function, FLOOR() does not round up; it simply drops the decimal portion and returns the greatest integer that is less than or equal to the number.

Syntax:

FLOOR(value)

Example

The following query removes the decimal values by truncating them:

SELECT NAME, FLOOR(MARKS) AS MARKS FROM geeksforgeeks;

Output

Explanation:

• 80.9 is truncated to 80.
• 86.89 becomes 86, discarding the fractional part.
• No rounding occurs; the values are simply reduced to the nearest lower integer.

3. Using the CAST() Function

The CAST() function is used to convert a value from one data type to another. If we want to remove decimal places, we can cast the floating-point number to an integer type, which automatically removes any decimal values.

Syntax:

CAST( value as datatype)

Example

The following query converts the MARKS column to integer values:

SELECT NAME, CAST(MARKS as INT) AS MARKS FROM geeksforgeeks;

Output

Explanation:

• 80.9 becomes 80.
• 86.89 is converted to 86.
• Similar to FLOOR(), this method truncates the decimals without rounding

Conclusion

Removing or adjusting decimal values in SQL can be efficiently handled using functions like ROUND(), FLOOR(), and CAST(). Each function has its unique purpose: the ROUND() function helps round values to a specific number of decimal places, which is useful for generating more readable outputs. The FLOOR() function removes decimals by rounding down to the nearest integer, making it suitable for scenarios where you need to ensure values do not exceed a certain threshold.