Open In App

SQL Query to Remove Decimal Values

Improve
Improve
Improve
Like Article
Like
Save Article
Save
Share
Report issue
Report

Decimal values are those values that have “float” as a datatype.

There are various methods to remove decimal values in SQL:

  • Using ROUND() function: This function in SQL Server is used to round off a specified number to a specified decimal places
  • Using FLOOR() function: It returns the largest integer value that is less than or equal to a number. 
  • Using CAST() function: The explicit conversion has to be done in SQL Server using Cast or Convert function.

STEP 1: Creating a database

Use the below SQL statement to create a database called geeks:

Query: 

CREATE DATABASE geeks;

Step 2:  Using the database 

Use the below SQL statement to switch the database context to geeks:

Query:

USE geeks;

Step 3: Table definition

We have the following geeks for geeks table in our geek’s database.

Query:

CREATE TABLE geeksforgeeks(
NAME VARCHAR(10),
MARKS float);

Step 4: Insert data into a table

Query:

INSERT INTO geeksforgeeks VALUES ('ROMY',80.9),('MEENAKSHI',86.89),('SHALINI',85.9),('SAMBHAVI', 89.45);

Step 5:Check value of the table

Content of the table can be viewed using the SELECT command.

Query:

SELECT * FROM geeksforgeeks;

Step 6:Use function to remove decimal values

By using Round() function

  • ROUND(): This function rounds a number to the specified decimal places. If we want to remove all the decimal values, we will round it to decimal place 0.

Syntax:

ROUND(Value, decimal_place)

Query:

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

Output:

80.0 is rounded to 81 as 81 is the nearest integer value.

By using the FLOOR() function

  • FLOOR(): This function returns the largest integer value which is less than or equal to the value used as a parameter.

Syntax:

 FLOOR(value)

Query:

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

Output:

Here, 80.9 gets converted to 80, as FLOOR() returns a value less than or equal to the given value but can not return the value greater than the given one. 

By using CAST() function

  • CAST(): This function is used to convert the value into a specific data type.

Syntax:

CAST( value as datatype)

Query:

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

Output:

This gives results similar to the FLOOR() function. Results vary slightly according to the function used. One should choose according to the need.


Last Updated : 23 Sep, 2021
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads