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.