MySQL - FLOOR() Function

The FLOOR() function of MySQL is used to retrieve the closest (to positive infinity) integer value that is smaller than or equal to a particular number. This operation differs from approximation, as the value after the decimal point are neglected (unlike in approximation).

This function accepts an integer value as an argument and returns the largest integer smaller than the given value.

Syntax

Following is the syntax of MySQL FLOOR() function −

FLOOR(X)

Parameters

This function takes a numeric value as a parameter.

Return Value

This function returns the largest integer less than or equal to the given value.

Example

The following query returns the largest integer less than or equal to 22.3, which is 22 −

SELECT FLOOR(22.3) As Result;

Output

This will produce the following result −

Result
22

Example

You can also pass a function as a value to this function as shown below. The value of PI() function is 3.14 −

SELECT FLOOR(PI()) As Result;

Output

This will produce the following result −

Result
3

Example

Here, we are fetching the largest integer less than or equal to -105.0238 −

SELECT FLOOR(-105.0238) As Result;

Output

This will produce the following result −

Result
-106

Example

Apart from numerical values, you can also pass values to this function as strings −

SELECT FLOOR("2254.554") As Result;

Output

The output is displayed as follows −

Result
2254

Example

In the example below, we are creating a MySQL table named CUSTOMERS using the CREATE statement as follows −

CREATE TABLE CUSTOMERS (
   ID INT AUTO_INCREMENT,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

The following query inserts 7 records into the above created table −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES 
(1, 'Ramesh', 32, 'Ahmedabad', 2000.56 ),
(2, 'Khilan', 25, 'Delhi', 1500.33 ),
(3, 'Kaushik', 23, 'Kota', 2000.66 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.95 ),
(5, 'Hardik', 27, 'Bhopal', 8500.99 ),
(6, 'Komal', 22, 'Hyderabad', 4500.11 ),
(7, 'Muffy', 24, 'Indore', 10000.50 );

Execute the below query to fetch all the inserted records in the CUSTOMERS table −

Select * From CUSTOMERS;

Following is the CUSTOMERS table −

ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.56
2 Khilan 25 Delhi 1500.33
3 Kaushik 23 Kota 2000.66
4 Chaitali 25 Mumbai 6500.95
5 Hardik 27 Bhopal 8500.99
6 Komal 22 Hyderabad 4500.11
7 Muffy 24 Indore 10000.50

Now, we are using the MySQL FLOOR() function on the SALARY column to return the largest integer value that is less than or equal to each value in the SALARY column −

SELECT ID, NAME, FLOOR(SALARY) FROM CUSTOMERS;

Output

The output for the query above is produced as given below −

ID NAME SALARY
1 Ramesh 2000
2 Khilan 1500
3 Kaushik 2000
4 Chaitali 6500
5 Hardik 8500
6 Komal 4500
7 Muffy 10000
Advertisements