Functions for SQL rounding

SQL Rounding Action April 02, 2009 Thursday 10:01 before the use of this method has been documented, but today's use of the time is still forgotten, in fact, usually used to take the whole operation there are several cases, a simple rounding, Regardless of what is behind the decimal point, such as 12.21 or 12.68 is 12, so the floor (12.21), the parameters can be an expression, can be a number, can be a variable, and there are different restrictions on the rounding operation, If rounding is required and how many significant digits are left behind the decimal point, then the round function is the complete notation:
ROUND (numeric_expression, length [, function])
Parameters
Numeric_Expression
An expression of the exact numeric or approximate numeric data type category (except for the bit data type).
Length
Is the precision numeric_expression will be rounding. Length must be tinyint, smallint, or int. When length is positive, numeric_expression rounds to the number of decimal digits specified by length. When length is negative, numeric_expression is rounded to the left of the decimal point as specified by length.
function
Is the type of action to perform. The function must be tinyint, smallint, or int. If you omit the value of function or function as 0 (the default), Numeric_Expression is rounded. When a value other than 0 is specified, numeric_expression is truncated.
Cases:
SELECT ROUND (150.75, 0)
151.00
SELECT ROUND (150.75, 0, 1)
150.00

Default does not add parameters to the same as the first example, the default rounding.

Rounding functions in SQL Floor, ROUND, Ceil, TRUNC, sign (2009-12-29-17:13:12)
Tags: integer function fromabs Decimal value education Category: SQL
1 trunc (value,precision) intercepts a number by precision (precision) and does not perform rounding operations.
2 round (value,precision) Enter a value based on the given precision (precision).
3 Ceil (value) produces the smallest integer greater than or equal to the specified value (value).
4 floor (value), in contrast to Ceil (), produces the smallest integer less than or equal to the specified value (value).
5 sign (value) is opposite to the absolute value function abs (). ABS () gives the amount of the value rather than its symbol, and sign (value) gives the value a symbol rather than a quantity.


1, returns the largest integer greater than or equal to x:
Sql> Select Ceil (23.33) from dual;


Ceil (23.33)
-----------
24


2, returns the largest integer equal to or less than x:
Sql> Select Floor (23.33) from dual;


Floor (23.33)
------------
23

3, returns the x value of the Y-bit rounded to the right of the decimal point: Rcund (X,[y])
Sql> Select Round (23.33) from dual;


ROUND (23.33)
------------
23


4, return truncated to Y decimal place x Value: Trunc (X,[y])
Sql> Select Trunc (23.33) from dual;


TRUNC (23.33)
------------
23


5, returns the symbol of X
Sql> Select sign ( -23.33) from dual;


Sign (-23.33)
------------
-1

Functions for SQL rounding