Home > T-SQL > Trim or Round a float value – SQL Math Function

## Trim or Round a float value – SQL Math Function

ROUND() function is used to round or trim a numeric value with precision to a specified length.

Syntax

ROUND ( numeric_expression , length [ ,function ] )

This function always returns a value. It could be 0 (zero) if there is an error in the argument or the rounded numeric value. It’s return type is decided by the argument value type with little change. Data Type mapping is given below.

Numeric Expression  data type = Output data type

Tinyint or smallint or int = int
bigint = bigint
Decimal(p,s) or numeric (p, s) = decimal(p, s)
Money or smallmoney =     money
float or real = float

The round operation can be done on any side of the numeric value such as left (whole number) or right (precision). If the length value is positive number then it will round the precision value or if the length value is negative number then it will round the whole number.

The function value is the optional argument and it is used to tell the function to truncate or to round the given value. If the function value is 0 then it is round or if it is positive integer then it will be truncated.

Example

–Simple round operation. It rounds 2 precision value of a numeric value.
select round(100.234, 2)
Output: 100.230

select round(100.235, 2)
Output: 100.240

–Round the whole number
select round(145.999, -1)
Output: 150.000

select round(145.999, -2)
Output: 100.000

–Truncating numbers
SELECT ROUND(150.75, 0, 1)
Output: 150.00

SELECT ROUND(150.75, 1, 1)
Output: 150.70

SELECT ROUND(150.75, 1, 0)
Output: 150.80

There are other math functions such as FLOOR and CEILING to round the number with fixed precision. But, Round function is used to round the given value to the user defined length for both left and right of a numeric value.