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:
CREATE DATABASE geeks;
Step 2: Using the database
Use the below SQL statement to switch the database context to geeks:
Step 3: Table definition
We have the following geeks for geeks table in our geek’s database.
CREATE TABLE geeksforgeeks(
Step 4: Insert data into a table
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.
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.
SELECT NAME, ROUND(MARKS,0) AS MARKS FROM geeksforgeeks;
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.
SELECT NAME, FLOOR(MARKS) AS MARKS FROM geeksforgeeks;
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.
CAST( value as datatype)
SELECT NAME, CAST(MARKS as INT) AS MARKS FROM geeksforgeeks;
This gives results similar to the FLOOR() function. Results vary slightly according to the function used. One should choose according to the need.
Unlock the Power of Placement Preparation!
Feeling lost in OS, DBMS, CN, SQL, and DSA chaos? Our Complete Interview Preparation
Course is the ultimate guide to conquer placements. Trusted by over 100,000+ geeks, this course is your roadmap to interview triumph.
Ready to dive in? Explore our Free Demo Content and join our Complete Interview Preparation