SQL FLOOR() Function
The SQL FLOOR() is a function, and return previous whole integer (no fractional digits) value that is equal to or less than a given input floating number from query result.
The SQL FLOOR() function will always goes to the previous whole integer value.
Ex:
- Floor(6) = result is "6"
- Floor(3.77) = result is "3"
- Floor(5.12) = result is "5"
- Floor(1.9) = result is "1"
The SQL Floor() function is supports only numeric column or an numeric field based expression.
It can be used in SELECT statement as well in where clause.
Related Links
SQL FLOOR() Syntax
For SQL SERVER / ORACLE / MY SQL
The basic syntax is for numeric value or numeric expression:
SELECT FLOOR(number);
The below syntax is for a given table numeric column or field from a specific table.
SELECT FLOOR(Column_name) FROM table_name;
SQL FLOOR() Example - Using Expression Or Formula
The following SQL SELECT statement returns the previous whole integer(without fraction digits) value of a given input floating number or expression
SELECT FLOOR(3.8) AS 'Expr';
The result of above query is:
| Expr |
|---|
| 3 |
SQL FLOOR() Function More Example
| Input Value | Result |
|---|---|
| Floor(5.3) | 5 |
| Floor(6.8) | 6 |
| Floor(-7.7) | -8 |
| Floor((-4 * 1.2) + 3) | -2 |
Sample Database Table - Customer
| CID | CName | Balance | City |
|---|---|---|---|
| 111 | Suresh Babu | 128.66 | Nasik |
| 222 | Azagu Varshith | 560 | Madurai |
| 333 | Haris Karthik | 673.88 | Bangalore |
| 444 | Kishor Kumar | -188.63 | Chennai |
SQL FLOOR() Example - With Table Column
The following SQL SELECT statement find the previous whole integer(without fraction digits) value of a given table column "Balance" from the "Customer" table:
SELECT CName,
FLOOR(Balance) As 'New Balance'
FROM Customer;
The result of above query is:
| CName | New Balance |
|---|---|
| Suresh Babu | 128 |
| Azagu Varshith | 560 |
| Haris Karthik | 673 |
| Kishor Kumar | -189 |
SQL FLOOR() Example - Using WHERE Clause
The following SQL SELECT statement find the previous whole integer value of a given input number for creating condition (greater than the given value) on table column "Balance" from the "Customer" table:
SELECT CID, CName, Balance
FROM Customer
WHERE Balance > FLOOR(128.70);
The result of above query is:
| CID | CName | Balance |
|---|---|---|
| 111 | Suresh Babu | 128.66 |
| 222 | Azagu Varshith | 560 |
| 333 | Haris Karthik | 673.88 |
Note: The Floor(128.70) function will return a value of "128". So now the result set contains and display records or rows which has balance more than "128" on column "Balance" from the "Customer" table.
Related Links