SQL Query to Remove Decimal Values

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 Beginner:

Query: 

CREATE DATABASE Beginner;

Step 2:  Using the database 

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

Query:

USE Beginner;

Step 3: Table definition

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

Query:

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

Step 4: Insert data into a table

Query:

INSERT INTO w3wiki 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 w3wiki;

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 w3wiki;

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 w3wiki;

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 w3wiki;

Output:

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